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

[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?

 

[Mike H]

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

 

[J Sedoff]

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

 

[sifiso062]

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

J Sedoff you a genius!!!!!

 

[Don Guillett]

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?

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