How to remove few texts from a cell

Feroz90

Board Regular
Joined
Apr 25, 2019
Messages
52
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
with Power Query

Code:
[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]

Column1Column1
1 of 100 1 of 200 1 of 500
100​
200​
500​
 
Last edited:
Upvote 0
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.
 
Upvote 0
how about, with a formula


Book1
AB
21 of 100 1 of 200 1 of 500100
3200
4500
End
Cell Formulas
RangeFormula
B2=TRIM(MID(SUBSTITUTE($A$2,"1 of", REPT(" ",100)),ROW($A1)*100,100))+0
 
Last edited:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
As of now, I am ok with one cell, I just wanted to split the texts and update the values in a list.
 
Upvote 0
How about
Code:
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
 
Upvote 0
Thank you Fluff, but i am getting the number with one cell below, like 300 in B2 & 100 in B4.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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