# Data substitution in a spreadsheet

#### ThomasOtte

##### New Member
Hi everyone,

new here, so bear with me.

I have a spreadsheet full of letters A,B,C...N,O in separate cells
I would like to replace these letters by names. Just as there are 15 letters, I have 15 names to match.

A = John
B= Pete
C= Dave
etc.

Names are placed into the worksheet too, to be able to refer to them.

I did this manually with the "Search and Replace function" but I am sure that Excel has a much more clever way to do this than do the Replace stuff 15 separate times.

I can't figure it out and it frustrates me beyond all recognition...

Can anyone help me out? I have no experience at all with VBA.

Best,
Thomas

/The file is a schedule for students working shifts in teams of 2. A colleague made this schedule for me with special software. Could have inserted names instead of letters in the first place, but what's the fun in that.../

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### ParamRay

##### Well-known Member
.
.

You could adapt something like this:

Code:
``````Sub ReplaceValues()

Dim Arr(3, 2) As String
Dim Rng As Range
Dim i As Byte

'set values to be replaced
Arr(0, 0) = "A"
Arr(1, 0) = "B"
Arr(2, 0) = "C"

'set replacement values
Arr(0, 1) = "Alpha"
Arr(1, 1) = "Bravo"
Arr(2, 1) = "Charlie"

'loop through cells
'and replace values
For Each Rng In ActiveSheet.UsedRange
For i = LBound(Arr, 1) To UBound(Arr, 1)
If LCase(Rng.Value) = LCase(Arr(i, 0)) Then
Rng.Value = Arr(i, 1)
End If
Next i
Next Rng

End Sub``````

Last edited:

#### ThomasOtte

##### New Member
Thanks very much for your help!
How do I use this in my spreadsheet? I have never used anything like this...

#### Rick Rothstein

##### MrExcel MVP
How do I use this in my spreadsheet? I have never used anything like this...
HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste gpeacock's code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ReplaceValues) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

#### ThomasOtte

##### New Member

Can I just copy the code and add on to it? The list of items to be replaced is 15, so can I go:

Arr (2,0) = "C"
Arr (3,0) = "D"
etc

and

Arr (2, 1) = "Charlie"
Arr (3, 1) = "Delta"
etc

until I have reached "O" to be replaced with "Oscar"? Quickly tried it a minute ago and it ran an error somewhere... didn't check properly though for any typos.
Should this work or do I have to adjust more than just adding to the list in the same format?

Thanks!!

#### Rick Rothstein

##### MrExcel MVP
Can I just copy the code and add on to it? The list of items to be replaced is 15, so can I go:

Arr (2,0) = "C"
Arr (3,0) = "D"
etc

and

Arr (2, 1) = "Charlie"
Arr (3, 1) = "Delta"
etc

Yes, but you must change this line of code to account for the extra items in the array...

Dim Arr(3, 2) As String

The count starts from 0 (not 1), so for 15 letter/names you would need to replace the 3 with 14

#### ThomasOtte

##### New Member
Worked like clockwork. Thank you so much for your help!!

Anyone know how to do this with regular formulas, just for the fun of it?

Replies
13
Views
131
Replies
1
Views
88
Replies
1
Views
26
Replies
3
Views
35
Replies
3
Views
31