Need Help in Data Formating

[Anonymous]

Hello,

I have a column that contains special character as well as middle space and number, i am looking to extract only number without getting any middle space in it.

Please help, i tried all method to get the same.

Suresh

[Greg_Deckler]

@Anonymous Well, in that particular case you could do:

Text.Replace(Text.Middle([Column1],1,10)," ","")

[Anonymous]

Hi Sir,

My reference column is "Ref ID" from where i am trying you extract only number, however not able to go through form both solution i tried - "Solution 1" and "Solution 2".

Below which i tried...

=Text.Replace(Text.Middle([Ref ID],1,10)," ","")),

= Text.Select([Ref ID],{"0".."9"}))

Please help.

Thanks

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

Because your data contains 2 types of data which causes such error,using below expression instead:

= Table.TransformColumnTypes(Table.AddColumn(#"Appended Query", "Custom", each Text.Select(Text.From([Column1]),{"0".."9"})),{{"Custom", Int64.Type}})

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my raeply as a solution!

[wdx223_Daniel]

=Text.Remove(Text.From([Ref ID]??"")," ")

[Jakinta]

If you change [Ref ID] column type to text, it should work.

or

= Text.Select( Text.From ( [ #"Ref ID" ]) , {"0".."9"} )

[Greg_Deckler]

@Anonymous Sample data?

[Anonymous]

Sir, its like (0012345 78A. In this we have special character and albhabet as well as spacin between.