MrExcel Publishing
Your One Stop for Excel Tips & Solutions

how do i truncate first few characters in column of numbers


Posted by Dwight Hamilton on March 08, 2001 6:36 AM

I want to erase the first 3 digits in a long column of 13 digit numbers. First three digits are all the same (069; numbers vary thereafter. Don't want to use find/replace because 069 appears again in some of the numbers. Don't care whether the final result is "text" or "number". Thought that the text function "Right" might be the answer, but couldn't get it to work since all of the numbers are different, other than the leading 3 digits.


Posted by Celia on March 08, 2001 6:58 AM


For numbers up to 103 digits, to return text :-
=MID(A1,4,100)
To return numbers :-
=MID(A1,4,100)*1
Celia

Posted by Aladin Akyurek on March 08, 2001 6:59 AM

Dwight,

In B1 enter =RIGHT(A1,LEN(a1)-3)

and copy down this as far as needed.

Aladin

Posted by Bruce on March 08, 2001 7:00 AM

=RIGHT(A1,10)should work providing all numbers are 13 digits. If the first digits of the string are "0"'s they will still be displayed.

And a tip I just learned from Mark, is
=RIGHT(A1,10)+ 0 will make the string a number, so leading "0's will be lost.

Posted by Mark W. on March 08, 2001 7:09 AM

Here's how... using Text to Column Command...

1. Select your column
2. Choose the Data Text to Column... menu command
3. Choose Fixed width at Step 1 of 3, and press Next>
4. At Step 2 click between the 3rd and 4th column
in the data preview window and press Next>
5. At Step 3 assign the Do not import column (skip)
format to the 1st column, the Text format to the
2nd (if desired), and press Finish.

Posted by Dwight Hamilton on March 08, 2001 7:39 AM

Very helpful, thanks. Would like to incorporate in a macro

That worked great. Since the situation occurs repetitively in lots of different spreadsheets, would like to create a macro to use after highlighting column (or portions of column) containing the leading digits. Tried this:

Selection.TextToColumns Destination:=Range("C6"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(3, 1))
Range("B6").Select

But it ties back to the range selected when I recorded the macro. Can you edit so macro will work on any range selected b4 running macro?
Thanks.

Posted by Mark W. on March 08, 2001 7:46 AM

Re: Very helpful, thanks. Would like to incorporate in a macro

Selection.TextToColumns Destination:=Selection, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 9), Array(3, 1))

Posted by Dwight Hamilton on March 08, 2001 8:21 AM

Perfecto! Thanks!