Stripping numbers from a cell

[Bennets04]

Hi All,
What would be the best formula to use to get the data from the below.... Im looking at just grabbing data in the [ ]'s from over 5,000 lines of data

Roshani [ RL52486 ] I'm looking to just get RLr2486
Ozlem [ OK51419 ] I'm looking to get OK51419
Robert [ RConville ] I'm looking to get RConville

Many thanks
Steve

 

[Joe4]

Here is one way, for an entry in cell A1:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"[",REPT(" ",200)),"]",REPT(" ",200)),200,100))

 

[Bennets04]

That is insane! haha
How on earth does that even work!! Thank you so much

 

[Joe4]

You are welcome.
The formula does look a little crazy, but actually works on a simple premise.
We are simply replacing the "[" and "]" signs with 200 spaces each.
Then, we are taking the middle 100 characters (so you will have what you want surrounded by a bunch of spaces on each side).
We then just use the TRIM function to get rid of all the beginning and ending spaces.

 

[jtakw]

Hi,

Another way, slightly shorter:

Book1
	A	B
1	Roshani [ RL52486 ]	RL52486
2	Ozlem [ OK51419 ]	OK51419
3	Robert [ RConville ]	RConville
Sheet205

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"]",""),"[",REPT(" ",255)),255,255))

 

[Rick Rothstein]

Here is another somewhat shorter formula...

=TRIM(MID(LEFT(A1,FIND("]",A1)-1),FIND("[",A1)+1,99))

 

[Scott Huish]

Also:
=TRIM(SUBSTITUTE(MID(A2,FIND("[",A2&"[")+1,99),"]",""))

Excel 2010
	A	B
2	Roshani [ RL52486 ]	RL52486
3	Ozlem [ OK51419 ]	OK51419
4	Robert [ RConville ]	RConville
5
Sheet1

Cell Formulas
Range	Formula
B2	=TRIM(SUBSTITUTE(MID(A2,FIND("[",A2&"[")+1,99),"]",""))
B3	=TRIM(SUBSTITUTE(REPLACE(A3,1,FIND("[",A3&"["),""),"]",""))
B4	=TRIM(SUBSTITUTE(REPLACE(A4,1,FIND("[",A4&"["),""),"]",""))
B5	=TRIM(SUBSTITUTE(REPLACE(A5,1,FIND("[",A5&"["),""),"]",""))

 

[Bennets04]

Good morning,

Thank you for your support yesterday. How about stripping the [ **** ] out completely with a formula?

UK Customer - Store - Bedford [ 891 ]
UK Customer - Store - St Helens [ 978 ]
UK Customer - Store - Covent Garden [ 1338 ]
UK Customer - Store - Covent Garden [ 1338 ]

So when I drag the formula down it will just provide

UK Customer - Store - Bedford
UK Customer - Store - St Helens
UK Customer - Store - Covent Garden

Thank you so much for your support

 

[jtakw]

Hi,

Use this:

Book1
	A	B
1	UK Customer - Store - Bedford [ 891 ]	UK Customer - Store - Bedford
2	UK Customer - Store - St Helens [ 978 ]	UK Customer - Store - St Helens
3	UK Customer - Store - Covent Garden [ 1338 ]	UK Customer - Store - Covent Garden
4	UK Customer - Store - Covent Garden [ 1338 ]	UK Customer - Store - Covent Garden
5	UK Customer - Store - Covent Garden-St Helens[123-456 1338 ]	UK Customer - Store - Covent Garden-St Helens
Sheet202

Cell Formulas
Range	Formula
B1	=TRIM(REPLACE(A1,FIND("[",A1),255,""))

Formula copied down.

 

[Bennets04]

Thank you mate!

Worked a treat!