How to remove few texts from a cell

[Feroz90]

Hi All,

How to remove few texts from a cell and list down the values in separate cells

Example: 1 of 100 1 of 200 1 of 500

I want to remove the "1 of" from the cell and list down the values in horizontal position one by one as

A2 = 100
A3 = 200
A4 = 500

Can anyone please help.

Thanks

 

[sandy666]

with Power Query

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SelectRows(Table.TransformColumnTypes(Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("1 of ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),{{"Column1", Int64.Type}}), each ([Column1] <> null))
in
    Split[/SIZE]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]1 of 100 1 of 200 1 of 500[/td][td][/td][td=bgcolor:#E2EFDA]

100​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]

200​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]

500​

[/td][/tr]
[/table]

 

[Feroz90]

Thank you,
But where do we need to paste this code, in a module or in a sheet.
I pasted in a module, but it shows in red.

 

[Fluff]

how about, with a formula

Book1
	A	B
2	1 of 100 1 of 200 1 of 500	100
3		200
4		500
End

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE($A$2,"1 of", REPT(" ",100)),ROW($A1)*100,100))+0

 

[sandy666]

Thank you,
But where do we need to paste this code, in a module or in a sheet.
I pasted in a module, but it shows in red.

you didn't read first line of the post, this is not vba but M-code for PowerQuery

What is Power Query?

 

[Feroz90]

Thank you both, If it is possible with VBA code, it will be helpful.

And I don't know why, the formula isn't working for me.

 

[Fluff]

Are you just going to be looking at one cell, or multiple cells?
If multiple cells, where are they & how do you want the result displayed?

 

[Feroz90]

As of now, I am ok with one cell, I just wanted to split the texts and update the values in a list.

 

[Fluff]

How about

Sub feroz90()
   Dim Sp As Variant
   Sp = Split(Replace(Range("A2").Value, "1 of ", ""))
   Range("B2").Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
End Sub

 

[Feroz90]

Thank you Fluff, but i am getting the number with one cell below, like 300 in B2 & 100 in B4.