Custom Format cell

Quietus

New Member
Joined
Feb 5, 2018
Messages
16
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Abid Fattani

New Member
Joined
Apr 20, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Feb 5, 2018
Messages
16
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Quietus()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",""21-""&@)", "@", .Address))
   End With
End Sub
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,062
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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

Code:
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

New Member
Joined
Feb 5, 2018
Messages
16
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Not for me it doesn't col B is the starting value & col A after the macro was run
+Fluff 1.xlsm
AB
1Part
221-AA
321-BB
421-CC
521-DD
621-EE
721-FF
821-SS
921-ZZZZ
10
1121-123123
1221-123.45123.45
Main


Can you post some samples of your data.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,136,301
Messages
5,674,963
Members
419,536
Latest member
Mohammed Jaffer

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
Top