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

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

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

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

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