Any excel Wizards available to help?

[franey]

I have a few excel data problems that I need assistance with, would anyone be able to assist please?

Basically (for now) I need a digit adding to the front of a 22 digit number and another digit removing within the number.

The numbers are all in column A and there are over 14000 of them, it would take ages for me to do it manually!!

Thanks.

 

[moonloops]

Adding a digit is easy, removing one could be more difficult - is it in the same position every time and is the original number a fixed length ?

 

[franey]

Hi thanks for the response yes in the same position every time.

 

[moonloops]

To add a number use ="X"&A1 in a cell next to the original number, were X is the digit you want to add and A1 is the source cell. You end up with a whole new column of numbers next to the original column.

 

[moonloops]

To remove the digit using similar method to above you split the original number up. For example if the digit you want to remove is at position 6:

=LEFT(A1,5)&RIGHT(A1,16) takes first 5 digits and joins it with last 16

 

[moonloops]

Once you are happy with the results copy the new number range and paste it over originals using paste, special, values only or some such..

 

[wemorgan]

CONCATENATE is quite useful when adding one cell string to another cell string.

DATA, TEXT TO COLUMNS is quite good for splitting a long string in to shorter ones.

Hope the above helps a little. I'm sure there must be a more elegant way of removing digits.

edit: Moonloops seems to have it covered

 

[franey]

moonloops, wemorgan, thank you both for your assistance, very much appreciated and saved me lots of time providing a solution to stock control.
Next time you need any MB or Audi / VW / BMW parts give me a nod, you will be very well looked after

 

[moonloops]

moonloops, wemorgan, thank you both for your assistance, very much appreciated and saved me lots of time providing a solution to stock control.
Next time you need any MB or Audi / VW / BMW parts give me a nod, you will be very well looked after

Glad to help, I will give you a nod if I need any Audi parts in future