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!

 

[bebo021999]

Book1
	A	B	C
1	RAW	OPTION 1	OPTION 2
2		(remove first 3 digits)	(Number from last alphabet letter)
3	18G4007	4007	4007
4	18G4008	4008	4008
5	18G4009	4009	4009
6	18G7980	7980	7980
7	18M18	0018	0018
8	18R4000	4000	4000
9	18R912	0912	0912
Sheet1

Cell Formulas
Range	Formula
B3:B9	B3	=RIGHT("0000"&MID(A3,4,10),4)
C3:C9	C3	=RIGHT("0000"&AGGREGATE(14,6,RIGHT(A3,{1,2,3,4})+0,1),4)

 

[TheWes]

Thank you. It works and I understand it.

 

[TheWes]

Hi everyone,

I need some additional help with formatting a set of raw data. Here’s what I’m working with:

Raw Data: (+- 6000 rows)


46U4296
47M47
4D100
4D392


Desired Output:
I want to extract the first two numbers from each entry, but if the second character is an alphabetic letter, I want to put a zero in front of the extracted number.
Here’s how I’d like it to look:

New data
046
047
04
04

Any suggestions on how to achieve this would be greatly appreciated!
I can only think about some nested function, but this should be able to be done easier.

 

[bebo021999]

Book1
	A	B
11	46U4296	046
12	47M47	047
13	4D100	04
14	4D392	04
Sheet1

Cell Formulas
Range	Formula
B11:B14	B11	="0"&AGGREGATE(14,6,LEFT(A11,{1,2,3,4})+0,1)

 

[Sanjeev1976]

You can also use the below formula :

="0"&LEFT(A11,MATCH(TRUE,ISERROR(VALUE(MID(A11,ROW(INDIRECT("1:"&LEN(A11))),1))),0)-1)

 

[TheWes]

Book1
	A	B
11	46U4296	046
12	47M47	047
13	4D100	04
14	4D392	04
Sheet1

Cell Formulas
Range	Formula
B11:B14	B11	="0"&AGGREGATE(14,6,LEFT(A11,{1,2,3,4})+0,1)

The solution above works for my initial desired output. Clear and timely. Thanks.

I did however make a mistake in my desired output.
I only want to put a zero in front of entries that only have 1 number before the alphabetic number. The other entries I simply want to extract the first 2 number characters.

Raw Data: (+- 6000 rows)
46U4296
47M47
4D100
4D392

Desired Output:
46
47
04
04

Thanks.

 

[TheWes]

You can also use the below formula :

="0"&LEFT(A11,MATCH(TRUE,ISERROR(VALUE(MID(A11,ROW(INDIRECT("1:"&LEN(A11))),1))),0)-1)

The solution above works for my initial desired output. Clear and timely. Thanks.
FYI only, I responded to bebo also with a slight tweak.

 

[Sanjeev1976]

You can try the below formula :

=IF(LEN(LEFT(A11,MATCH(TRUE,ISERROR(VALUE(MID(A11,ROW(INDIRECT("1:"&LEN(A11))),1))),0)-1))=1,"0"&LEFT(A11,MATCH(TRUE,ISERROR(VALUE(MID(A11,ROW(INDIRECT("1:"&LEN(A11))),1))),0)-1),LEFT(A11,MATCH(TRUE,ISERROR(VALUE(MID(A11,ROW(INDIRECT("1:"&LEN(A11))),1))),0)-1))

or you can use the below :

=TEXT(LEFT(A11,MATCH(TRUE,ISERROR(VALUE(MID(A11,ROW(INDIRECT("1:"&LEN(A11))),1))),0)-1),"00")

 

[TheWes]

You can try the below formula :

=IF(LEN(LEFT(A11,MATCH(TRUE,ISERROR(VALUE(MID(A11,ROW(INDIRECT("1:"&LEN(A11))),1))),0)-1))=1,"0"&LEFT(A11,MATCH(TRUE,ISERROR(VALUE(MID(A11,ROW(INDIRECT("1:"&LEN(A11))),1))),0)-1),LEFT(A11,MATCH(TRUE,ISERROR(VALUE(MID(A11,ROW(INDIRECT("1:"&LEN(A11))),1))),0)-1))

Thank you, I tried both syntax but both are not working sadly.