Extracting and Formatting Numbers from Data Rows

[TheWes]

Hi guys,

I need some help please.

I have many rows of data that look like this:


Raw code
18G4007
18G4008
18G4009
18G7980
18M18
18R4000
18R912

I would like to extract the numbers from the right until the alphabetic letter.
I want the extracted numbers to always be 4 digits. If there are only 2 or 3 digits before the alphabetic letter, I’d like to add 1 or 2 zeros in front, respectively.
To summarize, I’m looking for a solution that can transform the raw input:


Raw code
18G4007
18G4008
18G4009
18G7980
18M18
18R4000
18R912

New code
4007
4008
4009
7980
0018
4000
0912

Any guidance or syntax in Excel to achieve this would be greatly appreciated!

Thanks in advance!

 

[Sanjeev1976]

Can you provide some sample data for your above requirement

 

[bebo021999]

Bảng-kê-doanh-thu-chi-phí-T10.2024.xlsx
	A	B
10	46U4296	46
11	47M47	47
12	4D100	04
13	4D392	04
Sheet1

Cell Formulas
Range	Formula
B10:B13	B10	=RIGHT("00"&AGGREGATE(14,6,LEFT(A10,{1,2,3,4})+0,1),2)

 

[TheWes]

Thank you, great solution, I need to look into the aggregate function but this for sure works. Thanks.