need only data

sksanjeev786

Well-known Member
Joined
Aug 5, 2020
Messages
873
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
rawNumber
32%A, (%)32
32%ab,32
32AB, (No Space )32
32 AB(only has Space)32
1CD DD1
333%SX VV333

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
 
Upvote 0
rawNumber
32%A, (%)32
32%ab,32
32AB, (No Space )32
32 AB(only has Space)32
1CD DD1
333%SX VV333

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
 
Upvote 0
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

can you please check

Thanks.

Regards,
Sanjeev
 

Attachments

  • errir.png
    errir.png
    10.1 KB · Views: 9
Upvote 0
add header to your existing table (column)
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
then Close&Load

for more info
 
Upvote 0
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
 
Upvote 0
add header to your existing table (column)
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
then Close&Load

for more info
Thank you Sandyyy :):)
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top