Any excel Wizards available to help?

Page may contain affiliate links. Please see terms for details.

franey

Active Member
Joined
Nov 25, 2007
Messages
753
Location
Doncaster
Car
W201 190 2.5d, W124 300d 24v, W221 S350 AMG
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.
 
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 ?
 
Hi thanks for the response yes in the same position every time.
 
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.
 
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
 
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..
 
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 :)
 
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 :thumb:;)
 
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 :thumb:;)

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

Users who are viewing this thread

Back
Top Bottom