How to add a prefix to a column of numbers in an excel document?

  • Thread starter Thread starter Beth
  • Start date Start date
B

Beth

Our company is altering the code of our item numbers. is there a way to
delete one letter from a line of symbols and add a prefix to the front at the
same time?
 
Beth,

There are numerous ways but picking the best depends on the data layout. can
we see a sample from the column to be changed.

Mike
 
Do you mean you have a letter/symbol at the beginning of your text that you
wish to switch out with a new letter?

Like this:
"a100" --> "b100"

Then just use the formula =CONCATENATE("b", RIGHT(A2,LEN(A2)-1))
The Concatenate will allow you to add anything (replace the "b" with
whatever the new prefix code is) and the Right(A2,Len(A2)-1) will remove the
first symbol.

Initially you will have to put this in another column/row, but then you can
just copy and paste Special Values (right-click, "Paste Special," choose
"Values" then ok) into the old field and delete the new column (or you may
want to keep the old column as is in case you have stragglers using the old
system... I don't know).

Hope this helps, Jim
 
Do you mean you have a letter/symbol at the beginning of your text that you
wish to switch out with a new letter?

Like this:
"a100" --> "b100"

Then just use the formula =CONCATENATE("b", RIGHT(A2,LEN(A2)-1))
The Concatenate will allow you to add anything (replace the "b" with
whatever the new prefix code is) and the Right(A2,Len(A2)-1) will remove the
first symbol.

Initially you will have to put this in another column/row, but then you can
just copy and paste Special Values (right-click, "Paste Special," choose
"Values" then ok) into the old field and delete the new column (or you may
want to keep the old column as is in case you have stragglers using the old
system... I don't know).

Hope this helps, Jim
Click to expand...

J Sedoff you a genius!!!!!
 
Our company is altering the code of our item numbers. is there a way to
delete one letter from a line of symbols and add a prefix to the front at the
same time?
Click to expand...

If there are no other "b"'s in the number then simply select the column header and use data>replace>b with a....
 
Back
Top