Extract text from a string
 

I have the following cells

A1 = Joe Bloggs[20%]
A2 = Fred Astair[5%]

I would like to pull the percentage out of the name into another cell. At
the moment I have

MID(A1,SEARCH("[",A1)+1,3)

However this won't work for a percentage under 10, like 5%.
Also, there are occasions when there is no percentage in brackets, just the
person's name - is there a way of allowing for this without using an extra IF
statement?

Thanks,

Ellebelle

Extract text from a string
 

Ellebelle

Well I couldn't manage it without IF statements, will this do?

=IF(LEN(MID(A1,SEARCH("[",A1)+1,3))=1,"",IF(RIGHT(MID(A1,SEARCH("[",A1)+1,3),1)="]",MID(A1,SEARCH("[",A1)+1,2),MID(A1,SEARCH("[",A1)+1,3)))

Mike

"" wrote:

I have the following cells

A1 = Joe Bloggs[20%]
A2 = Fred Astair[5%]

I would like to pull the percentage out of the name into another cell. At
the moment I have

MID(A1,SEARCH("[",A1)+1,3)

However this won't work for a percentage under 10, like 5%.
Also, there are occasions when there is no percentage in brackets, just the
person's name - is there a way of allowing for this without using an extra IF
statement?

Thanks,

Ellebelle

Extract text from a string
 

Hi,

Try following formula...

=IF(ISERROR(SEARCH("[",A1)),"",SUBSTITUTE(SUBSTITUTE(MID(A1,SEARCH( "[",A1),(LEN(A1)-SEARCH("[",A1))+1),"[",""),"]",""))


--
Haldun Alay



"ellebelle" wrote in message
...
I have the following cells

A1 = Joe Bloggs[20%]
A2 = Fred Astair[5%]

I would like to pull the percentage out of the name into another cell. At
the moment I have

MID(A1,SEARCH("[",A1)+1,3)

However this won't work for a percentage under 10, like 5%.
Also, there are occasions when there is no percentage in brackets, just
the
person's name - is there a way of allowing for this without using an extra
IF
statement?

Thanks,

Ellebelle

Extract text from a string
 

Hi Ellebelle

Try
=SUBSTITUTE(MID(A1,SEARCH("[",A1)+1,3),"]","")

--
Regards

Roger Govier


"ellebelle" wrote in message
...
I have the following cells

A1 = Joe Bloggs[20%]
A2 = Fred Astair[5%]

I would like to pull the percentage out of the name into another cell.
At
the moment I have

MID(A1,SEARCH("[",A1)+1,3)

However this won't work for a percentage under 10, like 5%.
Also, there are occasions when there is no percentage in brackets,
just the
person's name - is there a way of allowing for this without using an
extra IF
statement?

Thanks,

Ellebelle

Extract text from a string
 

You can use TTC (Text To Columns), Delimited,
With the left square bracket as the delimiter.

After the primary separation, use Edit & Replace to eliminate the right
square bracket.

This gives you actual data, with *no* ensuing formulas to deal with.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"ellebelle" wrote in message
...
I have the following cells

A1 = Joe Bloggs[20%]
A2 = Fred Astair[5%]

I would like to pull the percentage out of the name into another cell. At
the moment I have

MID(A1,SEARCH("[",A1)+1,3)

However this won't work for a percentage under 10, like 5%.
Also, there are occasions when there is no percentage in brackets, just the
person's name - is there a way of allowing for this without using an extra
IF
statement?

Thanks,

Ellebelle