Custom Format cell

[Quietus]

I have a column with numbers and letters. I would like to add 21- in front of every one of them. I do not, how ever want to change every one manually as there are over 8000 entries. I would like to know if there is a Custom format that will do both number and letters.

Example: "21-"@ will change anything I have as a Letter to 21-ext....
Example: "21-"# will change anything I have as a number to 21-123.....

Is there a way to make it change 21- to both letters and numbers because I can only currently do one or the other.

Or maybe there is another way to make this happen not using this method. I just need everything in Column A to have a 21- in front of it.

Thanks a lot for any help and input.

P.s. I used the search and could not find something similar to this.

 

[Abid Fattani]

A simple way to concatenate the value with "21-".
For example if B1 has value "abc". On cell A1, you can put formula
="21-"&B1
Result will be "21-abc".

 

[Quietus]

Yes this would work if I was to add a new column. I was just hoping I could have it auto fill these in too. Thanks for your input!

 

[Fluff]

How about

Sub Quietus()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",""21-""&@)", "@", .Address))
   End With
End Sub

 

[kweaver]

You'll need a macro to change the column...such as:

Sub ColPrefix()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lr
 Cells(i, 1) = "21-" & Cells(i, 1)
Next i
End Sub

 

[Quietus]

How about

Sub Quietus()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",""21-""&@)", "@", .Address))
   End With
End Sub

This works for evryting thats a number fine. But everything with a letter stays the same.

 

[Fluff]

Not for me it doesn't col B is the starting value & col A after the macro was run

+Fluff 1.xlsm
	A	B
1	Part
2	21-A	A
3	21-B	B
4	21-C	C
5	21-D	D
6	21-E	E
7	21-F	F
8	21-S	S
9	21-ZZ	ZZ
10
11	21-123	123
12	21-123.45	123.45
Main

Can you post some samples of your data.

 

[Quietus]

I was able to get it to work. Thanks so much for the help.

 

[Fluff]

Glad we could help & thanks for the feedback.