need only data

[sksanjeev786]

Hi Team,

I have data in various formate. can I get any formula to find only data. There will be % space on random data but I need only data i.e 32 data can be in single digit as well. (instead of 32 it can be 8 with all below condtion)

32%A, (%)
32%ab,
32AB, (No Space )
32 AB(only has Space)

Thanks in Advance.
Regards,
Sanjeev

 

[sksanjeev786]

Assuming the numerical part is always the first thing in the string like your examples then this formula should also work for you, including with decimals.
Further, this one will not fail if rows are subsequently added at the top of the sheet.

sksanjeev786.xlsm
	B	C
1
2	raw
3	32%A, (%)	32
4	32%ab,	32
5	32AB, (No Space )	32
6	32 AB(only has Space)	32
7	1CD DD	1
8	333%SX VV	333
9	32%	32
10	0.32%xx vb	0.32
11	0.32	0.32
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=LEFT(B3,MATCH(TRUE,INDEX(ISERR(MID(SUBSTITUTE(B3,".",1)&"x",ROW(INDEX(A:A,1):INDEX(A:A,20)),1)+0),0),0)-1)+0

Thank you, Sir.

it work with decimal data as well.

Thank you again for your hard work.

Regards,
Sanjeev

 

[Peter_SSs]

You're welcome. Glad to contribute.

 

[sksanjeev786]

Assuming the numerical part is always the first thing in the string like your examples then this formula should also work for you, including with decimals.
Further, this one will not fail if rows are subsequently added at the top of the sheet.

sksanjeev786.xlsm
	B	C
1
2	raw
3	32%A, (%)	32
4	32%ab,	32
5	32AB, (No Space )	32
6	32 AB(only has Space)	32
7	1CD DD	1
8	333%SX VV	333
9	32%	32
10	0.32%xx vb	0.32
11	0.32	0.32
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=LEFT(B3,MATCH(TRUE,INDEX(ISERR(MID(SUBSTITUTE(B3,".",1)&"x",ROW(INDEX(A:A,1):INDEX(A:A,20)),1)+0),0),0)-1)+0

Hi Peter,

Just wanted to thank you again on the above formula.

I have shared with my team and they are really impressed and said one-word "Fabulous " with the formula.

But I team I asking about explanation about formula. Do we have any way so that I can explain on formula. ie. why we use Row and Index function at the end.

=LEFT(B3,MATCH(TRUE,INDEX(ISERR(MID(SUBSTITUTE(B3,".",1)&"x",ROW(INDEX(A:A,1):INDEX(A:A,20)),1)+0),0),0)-1)+0

Thank you in advance.
Sanjeev

 

[Peter_SSs]

Hi Peter,

Just wanted to thank you again on the above formula.

I have shared with my team and they are really impressed and said one-word "Fabulous " with the formula.

You and your team are welcome.

why we use Row and Index function at the end.

=LEFT(B3,MATCH(TRUE,INDEX(ISERR(MID(SUBSTITUTE(B3,".",1)&"x",ROW(INDEX(A:A,1):INDEX(A:A,20)),1)+0),0),0)-1)+0

The blue highlighted part of the formula produces the sequence of numbers 1,2,3,4,....,20
This is then used to look individually at the first 20 characters in the cell to locate the first one that is not a digit (excluding "." which has temporarily been substituted with a 1.
I have used 20 as I would not expect any leading number to be that long and perhaps we could have used a smaller number.

sandy666's formula does a similar thing with the much shorter ROW($1:$25)

I have gone for the longer version as it makes the formula more robust. To demonstrate, here in my formula in column C and sandy666's formula in col D. Both produce identical results for these (non-decimal) samples

sksanjeev786.xlsm
	B	C	D
1	32%A, (%)	32	32
2	32%ab,	32	32
3	32AB, (No Space )	32	32
4	32 AB(only has Space)	32	32
5	1CD DD	1	1
6	333%SX VV	333	333
7	32%	32	32
Sheet1

Cell Formulas
Range	Formula
C1:C7	C1	=LEFT(B1,MATCH(TRUE,INDEX(ISERR(MID(SUBSTITUTE(B1,".",1)&"x",ROW(INDEX(A:A,1):INDEX(A:A,20)),1)+0),0),0)-1)+0
D1:D7	D1	=SUMPRODUCT(MID(0&B1,LARGE(INDEX(ISNUMBER(--MID(B1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

But suppose that you, or another user, want to add some more information at the top of the sheet and subsequently insert one or more new rows. Here is one such result where the col C formula still returns the correct results but the col D one no longer does.

sksanjeev786.xlsm
	B	C	D
1
2	32%A, (%)	32	#NUM!
3	32%ab,	32	#NUM!
4	32AB, (No Space )	32	#NUM!
5	32 AB(only has Space)	32	#NUM!
6	1CD DD	1	#NUM!
7	333%SX VV	333	#NUM!
8	32%	32	#NUM!
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=LEFT(B2,MATCH(TRUE,INDEX(ISERR(MID(SUBSTITUTE(B2,".",1)&"x",ROW(INDEX(A:A,1):INDEX(A:A,20)),1)+0),0),0)-1)+0
D2:D8	D2	=SUMPRODUCT(MID(0&B2,LARGE(INDEX(ISNUMBER(--MID(B2,ROW($2:$26),1))*ROW($2:$26),0),ROW($2:$26))+1,1)*10^ROW($2:$26)/10)

 

[sksanjeev786]

You and your team are welcome.


The blue highlighted part of the formula produces the sequence of numbers 1,2,3,4,....,20
This is then used to look individually at the first 20 characters in the cell to locate the first one that is not a digit (excluding "." which has temporarily been substituted with a 1.
I have used 20 as I would not expect any leading number to be that long and perhaps we could have used a smaller number.

sandy666's formula does a similar thing with the much shorter ROW($1:$25)

I have gone for the longer version as it makes the formula more robust. To demonstrate, here in my formula in column C and sandy666's formula in col D. Both produce identical results for these (non-decimal) samples

sksanjeev786.xlsm
	B	C	D
1	32%A, (%)	32	32
2	32%ab,	32	32
3	32AB, (No Space )	32	32
4	32 AB(only has Space)	32	32
5	1CD DD	1	1
6	333%SX VV	333	333
7	32%	32	32
Sheet1

Cell Formulas
Range	Formula
C1:C7	C1	=LEFT(B1,MATCH(TRUE,INDEX(ISERR(MID(SUBSTITUTE(B1,".",1)&"x",ROW(INDEX(A:A,1):INDEX(A:A,20)),1)+0),0),0)-1)+0
D1:D7	D1	=SUMPRODUCT(MID(0&B1,LARGE(INDEX(ISNUMBER(--MID(B1,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

But suppose that you, or another user, want to add some more information at the top of the sheet and subsequently insert one or more new rows. Here is one such result where the col C formula still returns the correct results but the col D one no longer does.

sksanjeev786.xlsm
	B	C	D
1
2	32%A, (%)	32	#NUM!
3	32%ab,	32	#NUM!
4	32AB, (No Space )	32	#NUM!
5	32 AB(only has Space)	32	#NUM!
6	1CD DD	1	#NUM!
7	333%SX VV	333	#NUM!
8	32%	32	#NUM!
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=LEFT(B2,MATCH(TRUE,INDEX(ISERR(MID(SUBSTITUTE(B2,".",1)&"x",ROW(INDEX(A:A,1):INDEX(A:A,20)),1)+0),0),0)-1)+0
D2:D8	D2	=SUMPRODUCT(MID(0&B2,LARGE(INDEX(ISNUMBER(--MID(B2,ROW($2:$26),1))*ROW($2:$26),0),ROW($2:$26))+1,1)*10^ROW($2:$26)/10)

Thank you Peter for the detailed information. I will convey the above explanation to my team member and thanks a lot for your valuable time on this

Regards,
Sanjeev.

 

[Peter_SSs]