# need only data

#### sksanjeev786

##### Board Regular
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)

Regards,
Sanjeev

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### sandy666

##### Well-known Member
 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

Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
TS = Table.AddColumn(Source, "Number", each Text.Select([raw],{"0".."9"})),
Type = Table.TransformColumnTypes(TS,{{"Number", Int64.Type}})
in
Type``````

#### sksanjeev786

##### Board Regular
 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

Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
TS = Table.AddColumn(Source, "Number", each Text.Select([raw],{"0".."9"})),
Type = Table.TransformColumnTypes(TS,{{"Number", Int64.Type}})
in
Type``````

Thank you so much macro look great

Just wanted to check can we have this Excel formula if it possible.

Thank you.

Regards,
Sanjeev

#### sandy666

##### Well-known Member
Just wanted to check can we have this Excel formula if it possible
sure
btw. this is not a macro but M code

#### sksanjeev786

##### Board Regular

Thank you so much macro look great

Just wanted to check can we have this Excel formula if it possible.

Thank you.

Regards,
Sanjeev

Hi Sandy,

I am getting error while running macro

Thanks.

Regards,
Sanjeev

#### Attachments

• errir.png
10.1 KB · Views: 4

#### sandy666

##### Well-known Member
and post#2 what kind of the code it is

#### sksanjeev786

##### Board Regular

sure
btw. this is not a macro but M code

sure thanks for checking so can you please let me now how to apply in excel file

#### sandy666

##### Well-known Member
select whole range of data with header
go to Data tab then hit From Table (it will open Power Query Editor)
open Advanced Editor (from the ribbon)
replace code there with code copied from the post
be sure the Name of the table is the same as in the code (change in the code if necessary)
ok

##### Well-known Member
Hi
Try This VBA Code in a module
Data start from A2 down
VBA Code:
``````Sub Test()
Dim lr, i
Dim Rg, m As Object
With ActiveSheet
lr = .Cells(Rows.Count, 1).End(xlUp).Row
Set Rg = CreateObject("VBScript.RegExp")
Rg.Global = True
Rg.Pattern = "\d+"
For i = 1 To lr
If Rg.Test(Cells(i, 1)) Then
Set m = Rg.Execute(Cells(i, 1))
Cells(i, 1).Offset(, 1) = m(0)
End If
Next
End With
End Sub``````

#### sksanjeev786

##### Board Regular
select whole range of data with header
go to Data tab then hit From Table (it will open Power Query Editor)
open Advanced Editor (from the ribbon)
replace code there with code copied from the post
be sure the Name of the table is the same as in the code (change in the code if necessary)
ok

Thank you Sandyyy

Replies
14
Views
280
Replies
4
Views
87
Replies
8
Views
87
Replies
11
Views
230
Replies
8
Views
111