excel 2013-possible vba case-how to look up multiple strings separated by + signs in 1 cell, and replace with proper value

tolga

New Member
Joined
May 27, 2015
Messages
17
hello world,

I have a table like this. with the first column having main items and the second column having strings separated by plus signs

222222221111 + 3333 + 4444 + 6666
777777773333 + 4444 + 6666 + 1111

then, on another spreadsheet I have the strings in the first column and their descriptions in the second column
1111a1
3333a3
4444a4
6666a6


I want to be able to find every string value separated by the plus signs, look them up on the other sheet and replace them with their descriptions. I have no idea where to start. This is a huge spreadsheet by the way. Tens of thousands of lines. I could have a variable number of items separated by plus signs. this is driving me insane. I don't even know where to start, so I came here. :)

22222222a1 + a3 + a4 + a6
77777777a3 + a4 + a6 + a1
[/QUOTE]



is that clear enough or should I provide more data? dude called doc scared me a little about how to properly post. thanks. :)
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
See if this will work.
Code:
Sub replaceStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, rng As Range, lr As Long
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
lr = sh1.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh1.Range("B2:B" & lr)
    For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
        If c <> "" Then
            rng.Replace c.Value, c.Offset(0, 1).Value
        End If
    Next
End Sub

Copy the procedure to the standard code module 1. sh1 is the sheet with the plus signs in column B.

If you have problems posting, just refer to the guidelines provided elsewhere on this site.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top