Extracting data from a string

[richardthelionheart]

A7 31 . 13
A8 30 . 32
A5 * 30 . 41
A9 30 . 66
A8 * 30 . 06
A6 30 . 51

I have data which is formatted in a strange way.
There are spaces where you would not need or expect them to be.
Also there are some with * which is annoying because they are not
standardized to make it easy to extract the data in columns.
I want the data to look like this -
A7 31.13
A8 30.32
A5 30.66
A8 30.06
A6 30.51

Using a number of columns, I could probably find a way.
That would be cumbersome and untidy.
Maybe there's a neat solution to this using just one
formula ?? (or two)
Any help would be greatly appreciated.

 

[Scott T]

Try

=TRIM(SUBSTITUTE(A1,"*",""))

 

[sandy666]

or

=--SUBSTITUTE(SUBSTITUTE(A1,"*","")," ","")

 

[alansidman]

Here is a Power Query solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","*","",Replacer.ReplaceText,{"Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Changed Type1",{{"Column1.1", Text.Clean, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Column1.2", Text.Trim, type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Trimmed Text",{"Column1.1", "Column1.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

which is longer and more convoluted than the suggestions already offered with formula

 

[sandy666]

a little shorter

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace1 = Table.ReplaceValue(Source,"*","",Replacer.ReplaceText,{"src"}),
    Replace2 = Table.ReplaceValue(Replace1," ","",Replacer.ReplaceText,{"src"}),
    Type = Table.TransformColumnTypes(Replace2,{{"src", type number}})
in
    Type[/SIZE]

or even like this:

[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace = Table.ReplaceValue(Table.ReplaceValue(Source,"*","",Replacer.ReplaceText,{"src"})," ","",Replacer.ReplaceText,{"src"}),
    Type = Table.TransformColumnTypes(Replace,{{"src", type number}})
in
    Type[/SIZE]

 

[DanteAmor]

How about

=LEFT(A2,3)&SUBSTITUTE(SUBSTITUTE(MID(A2,4,LEN(A2))," ",""),"*","")

 

[richardthelionheart]

Many thanks to all contributors for giving up your time to solve this problem. I really appreciate it, Richard.

 

[sandy666]

You are welcome

Have a nice day

 

[DanteAmor]

Many thanks to all contributors for giving up your time to solve this problem. I really appreciate it, Richard.

I'm glad to help you. Thanks for the feedback.

 

[alansidman]

You are Welcome