need only data

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
150
Office Version
  1. 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
 

Some videos you may like

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
Joined
Oct 24, 2015
Messages
7,087
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
 

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
150
Office Version
  1. 2016
Platform
  1. Windows
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
 

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
150
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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: 4

sksanjeev786

Board Regular
Joined
Aug 5, 2020
Messages
150
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Oct 24, 2015
Messages
7,087
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
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
614
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Aug 5, 2020
Messages
150
Office Version
  1. 2016
Platform
  1. Windows
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 :):)
 

Watch MrExcel Video

Forum statistics

Threads
1,114,127
Messages
5,546,070
Members
410,726
Latest member
TheSardOz
Top