Do Multiple Replacements in Active Column Cells

[dj44]

folks,
good evening

I have been trying to replace values in a cell

i cant use CONCATENATION becuase i want to do something that makes the cell not work later if the formulas stuck there


A ........... B......................C ................ D
Apple............ Pear........ Orange............ # % $
Banana.......... Jam...........Peanut........... # % $


COLUMN D RESULT AFTER
Apple Pear Orange
Banana Jam Peanut


how can i replace the values on at a time in the Column D

I am trying to avoid formulas because i keep making mistakes and jamming my excel sheet.

Im sure i once had something similar but i cant find it anyhwere on the nets

I would like to do the substitutions with vba , i could search and replace one by one?

Maybe or is there a more sophisticated way to do it with a easy replace or an array?

please do help

thank you

[mancubus]

post your workbook pls

provide a list of old values and their corresponding new values.

[mdmackillop]

i can't use CONCATENATION because i want to do something that makes the cell not work later if the formulas stuck there

Not sure what that means, but try this; adjust 1 & 3 as required

Sub Test()
Dim c As Range, t As String, i As Long
Set c = ActiveCell
For i = 1 To 3
t = " " & c.Offset(, -i) & t
Next i
c.Formula = Trim(t)
c(2).Select
End Sub

[dj44]

Hello M,
nixe to see you

it worked for the first cell exactly what i was trying to do = to concatenate in Cell D the Values of Cell A,B,C minus the long formula

I was previously using something like this yikes
it s a mess

=SUBSTITUTE(SUBSTITUTE(B2,A2,CONCATENATE("#",A2)),A3,CONCATENATE("$",A3))


I need to make your code for my cell range D2 to D10 so it does the same to all of them

[dj44]

I Suppose I could have worded it better then Concatenate values of column into another column, but its so hard describing these things

But in case i needed to search and replace cells, i would need a vba formula to substitute #, % ,$

Im thinking out loud

[mdmackillop]

You've lost me. Can you post a workbook showing your data, result and your attempted solution

[dj44]

Hi M,

The reason they have placeholders in column D is becuase
I wanted the option to substitute the values, from A,B,C - minus the formula

I hope the illustration may be better to understand, i am just using a worksheet to test on at the moment




Substitute.jpg

[mdmackillop]

Best guess

Sub Test()
    Dim c As Range, r As Range, t As String, i As Long
    Dim x As Long
    Set c = ActiveCell
    Set r = Cells(1, 1).Resize(, 3)
    For i = 3 To 1 Step -1
    x = Application.Match(Mid(c, i, 1), r, 0)
        t = " " & Cells(c.Row, x) & t
    Next i
    c.Formula = Trim(t)
    c(2).Select
End Sub

[dj44]

Hello M,

thank you for your help let me do some testing and I'll be back

I hope i didnt make it too complicated as always happns with me
it started off well when i had one substituiton, then 2 by 3 i was all tangled up

[dj44]

Hello M,

Well substitution and concatenation isnt my cup of tea.

i forgot what i was trying to do after these darn formulas gave me a bashing, they just dont want to do their job,
i have a case for personal injury here

For Each Rng In ActiveSheet.Range("K5:K100")               '   & LstRw)
    Rng.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
    Next Rng
  
   '-- REMOVE Formulas
    With Range("K5:K" & LstRw)
    .Value = .Value
    End With

I inserted the formulas and then removed them, and then added another concateation dont ask

I am working on an array at the moment to do some search and replacements, but lets see how that goes i hate having to do one by one

[dj44]

A basic Array to do Search and Replacement

    Sub Search_Replace_Array()
   
   ' djs humble array
   'Searches in Cells and Replaces them with a value

    arrWords = Array("#", "%", "$", "&")
     
    For i = LBound(arrWords) To UBound(arrWords)

    ActiveSheet.Range("G1:G14").Select
       
    Selection.Replace What:=arrWords(i), Replacement:="Hello", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

         
    Next i

End Sub

now if i could just learn how to substitute 1 cell for another that would be a feat acheived

[mdmackillop]

What version of Excel are you using?

[mdmackillop]

You can't replace with different value at one go

    arrWords = Array("#", "%", "$", "&")
     
         For i = LBound(arrWords) To UBound(arrWords)
        For Each cel In ActiveSheet.Range("G1:G14").Cells
        cel.Replace What:=arrWords(i), Replacement:=cel.Offset(, i - 4).Value, LookAt:=xlPart
        Next cel
    Next i

[dj44]

Hello M,


As they say there are many ways to climb the mountain I think today ive been climbing a few wrong mountains

Well I get so distressed when I see the substitution formulas,
and concatenation because the last time I tried it my Excel Workbook went to 12 megabytes and that was a disaster

All I had the intention of doing was substituting values in cells, but I couldn't really explain what I was trying to do
But this is exactly what I was trying to do

your last bit of code did the job perfect.

Because the substitute formulas kept jamming every cell, and crashing my sheet

Im on office 365 excel 2016 but still its a notorious og excel it doesnt play nice

I'm really glad I don't have to spend a few hours trying to make a double loop for a search and replacement





Have a great evening and thanks again

Your a VBAX STAR

Problem solved

[mdmackillop]

FYI there is a new function in 2016 called TextJoin
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)


=TEXTJOIN(" ",,C12:F12)

[dj44]

Thank you M

I never heard of this before

I'll be a billion years old by the time I get to master even a quarter of all these complextacular functions that they have

Because I had so many columns and in order to get rid of some of them

I have to do the concatenation but then sometimes you have to do the substitution and I hate that combination

I'll look it up this text join one

thank you