Help with Truncating Alphabets from a cell in Excel

[Dhiren Sheth]

Hello,

I have a worksheet which contains a list of 400 stores in column A one below the other. The store number starts with DMB1234 and so on. I would like to remove the alphabets DMB from the store number and have just the number and do not want to do it manually for all the 400 stores. Is there a way I can do it using some formula.

Please help....

Thanks
Dhiren

 

[jasonb75]

Is it always 4 numbers?

=right(A1,4)

 

[MrKowz]

Is there always three letters at the beginning, and then a series of numbers?

 

[Dhiren Sheth]

Is it always 4 numbers?

=right(A1,4)

No the numbers may be more than 4 or more than 4 but maximum 5

 

[Dhiren Sheth]

Is there always three letters at the beginning, and then a series of numbers?

Hi Yes the there are always 3 letters at the beginning but the numbers could be 4 or 5 maximum

 

[Dhiren Sheth]

Hi Yes the there are always 3 letters at the beginning but the numbers could be 4 or 5 maximum

 

[jasonb75]

With a formula

=mid(a1,4,10)

that will allow up to 10 numbers.

For a permanent change withhout formula, select the range with the store numbers then run the code.

Sub removealpha()
    Selection.TextToColumns Destination:=Selection, DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 9), Array(3, 1)), TrailingMinusNumbers:=True
End Sub

 

[MrKowz]

Try:

=RIGHT(A1,LEN(A1)-3)

 

[pgc01]

Hi

Without formulas: use Text to Columns.

 

[snoopyhr]

Here something that works reagarless of number numbers or text as long as numbers and text are in that fashion "####AAAAA" as many as # or A (#=number, A=text)

Excel Workbook
	C	D	E
11		As text	As number
12	aaa12345	12345	12345
13	abcdg12345890	12345890	12345890
14	asxrtyu216589	216589	216589
15	xc90456783	90456783	90456783
Sheet5