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

 

[mohadin]

Dublicated

 

[sksanjeev786]

T

Function GDATA(r As Range)
    Dim Rg, m As Object
        Set Rg = CreateObject("VBScript.RegExp")
        Rg.Global = True
        Rg.Pattern = "\d+"
            If Rg.Test(r) Then
                Set m = Rg.Execute(r)
                If m.Count = 1 Then
                    GDATA = m(0) * 1
                Else
                    GDATA = m(1) * 1
                End If: End If
End Function

Perfect Shot!!!!!

Thanks for your help on this.

Have a great day Mohadin..

Regards
Sanjeev

 

[mohadin]

You are very welcome
And thank you for the feedback
Be happy

 

[sandy666]

try with (last rows should be 0.32 not 32 only)

Book1
	B	C	D
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		32
11	0.32		32
Sheet1

Cell Formulas
Range	Formula
D3:D11	D3	=GDATA(Table1[@raw])

eg.

raw	Number
32%A, (%)	32
32%ab,	32
32AB, (No Space )	32
32 AB(only has Space)	32
1CD DD	1
333%SX VV	333
32%	32
0.32%xx vb	0.32
0.32	0.32

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Text = Table.TransformColumnTypes(Source,{{"raw", type text}}),
    TS = Table.AddColumn(Text, "Number", each Text.Select([raw],{"0".."9","."})),
    DecNumber = Table.TransformColumnTypes(TS,{{"Number", type number}})
in
    DecNumber

 

[sksanjeev786]

try with (last rows should be 0.32 not 32 only)

Book1
	B	C	D
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		32
11	0.32		32
Sheet1

Cell Formulas
Range	Formula
D3:D11	D3	=GDATA(Table1[@raw])

Yes!!! it Work sandy

But also I am looking forward to seeing at the excel formula function if it is possible.

Thank you sandyy for your hard work and help on thissss!!!


Regards,
Sanjeev

 

[sandy666]

I am looking forward to seeing at the excel formula function if it is possible.

sure, good luck
and have a nice day

 

[sandy666]

try

=SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

but it doesn't work with decimals

 

[sksanjeev786]

try

=SUMPRODUCT(MID(0&B3,LARGE(INDEX(ISNUMBER(--MID(B3,ROW($1:$25),1))*ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

but it doesn't work with decimals

Sheer brilliance on display once again! Super happy get this functions.

Thank you so much for your hard work.

Really appreciate your work on this.

Regards,
Sanjeev

 

[sandy666]

You are welcome

 

[Peter_SSs]

But also I am looking forward to seeing at the excel formula function if it is possible.

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