Splitting the contents of a cell

[Guest]

I have been given a list of hospitals in the UK. The format is:

The Royal Infirmary of Edinburgh [Edinburgh]

As you can see the city the hospital is located in is enclosed in square
brackets. What I want to do is split it so that the hospital name is in
column A and the City is in column B

Is there anyway to do this without having to manually go through each one.

Also the entries are hyperlinks, does this make any difference?

 

[Dave Peterson]

I do this.

Copy Column A into column B (insert a new column B if you need to)

Select column A:
edit|replace
what: _[* (underscore = spacebar)
with: (leave blank)
replace all

Select column B
edit|Replace
what: *[
with: (leave blank)
replace all

With column B still selected
edit|replace
what: ]*
with: (leave blank)
replace all

I have been given a list of hospitals in the UK. The format is:

The Royal Infirmary of Edinburgh [Edinburgh]

As you can see the city the hospital is located in is enclosed in square
brackets. What I want to do is split it so that the hospital name is in
column A and the City is in column B

Is there anyway to do this without having to manually go through each one.

Also the entries are hyperlinks, does this make any difference?

 

[Guest]

Data....text to columns.... delimited
you might have to chose [ as a delimiter which will eliminate it from the
city but leave the ] at the end (which you could get rid of if you wished)

 

[Guest]

Yet another way........
In B1 put
=LEFT(A1,FIND("[",A1,1)-2)

IN C1 put
=MID(A1,FIND("[",A1,1)+1,FIND("]",A1,1)-FIND("[",A1,1)-1)

Copy both down, and do Copy > PasteSpecial > Values on columns B and C and
then delete column A if you wish.........

Vaya con Dios,
Chuck, CABGx3

 

[L. Howard Kittle]

Text to Columns would not accept "[" as a delimiter so I did a find/replace
with a coma. Worked fine and then another find/replace with nothing to get
rid of the "]" on the end.

HTH
Regards,
Howard

Data....text to columns.... delimited
you might have to chose [ as a delimiter which will eliminate it from the
city but leave the ] at the end (which you could get rid of if you wished)

I have been given a list of hospitals in the UK. The format is:

The Royal Infirmary of Edinburgh [Edinburgh]

As you can see the city the hospital is located in is enclosed in square
brackets. What I want to do is split it so that the hospital name is in
column A and the City is in column B

Is there anyway to do this without having to manually go through each
one.

Also the entries are hyperlinks, does this make any difference?

 

[Gord Dibben]

Strange doings Howard.

Excel 2003 Text to Columns accepted the " [ "as a separator.


Gord Dibben MS Excel MVP

Text to Columns would not accept "[" as a delimiter so I did a find/replace
with a coma. Worked fine and then another find/replace with nothing to get
rid of the "]" on the end.

HTH
Regards,
Howard

Data....text to columns.... delimited
you might have to chose [ as a delimiter which will eliminate it from the
city but leave the ] at the end (which you could get rid of if you wished)

I have been given a list of hospitals in the UK. The format is:

The Royal Infirmary of Edinburgh [Edinburgh]

As you can see the city the hospital is located in is enclosed in square
brackets. What I want to do is split it so that the hospital name is in
column A and the City is in column B

Is there anyway to do this without having to manually go through each
one.

Also the entries are hyperlinks, does this make any difference?

 

[L. Howard Kittle]

Strange indeed! Tried it again and no problem.

Hmmm, I had checked Other and entered a [ and it would not show in the box,
tried several times and no go. But now all is fine.

Regards,
Howard

Strange doings Howard.

Excel 2003 Text to Columns accepted the " [ "as a separator.


Gord Dibben MS Excel MVP

Text to Columns would not accept "[" as a delimiter so I did a
find/replace
with a coma. Worked fine and then another find/replace with nothing to
get
rid of the "]" on the end.

HTH
Regards,
Howard

Data....text to columns.... delimited
you might have to chose [ as a delimiter which will eliminate it from
the
city but leave the ] at the end (which you could get rid of if you
wished)



:

I have been given a list of hospitals in the UK. The format is:

The Royal Infirmary of Edinburgh [Edinburgh]

As you can see the city the hospital is located in is enclosed in
square
brackets. What I want to do is split it so that the hospital name is
in
column A and the City is in column B

Is there anyway to do this without having to manually go through each
one.

Also the entries are hyperlinks, does this make any difference?

 

[Gord Dibben]

Thanks for the update.

JOOTT


Gord

Strange indeed! Tried it again and no problem.

Hmmm, I had checked Other and entered a [ and it would not show in the box,
tried several times and no go. But now all is fine.

Regards,
Howard

Strange doings Howard.

Excel 2003 Text to Columns accepted the " [ "as a separator.


Gord Dibben MS Excel MVP

Text to Columns would not accept "[" as a delimiter so I did a
find/replace
with a coma. Worked fine and then another find/replace with nothing to
get
rid of the "]" on the end.

HTH
Regards,
Howard

Data....text to columns.... delimited
you might have to chose [ as a delimiter which will eliminate it from
the
city but leave the ] at the end (which you could get rid of if you
wished)



:

I have been given a list of hospitals in the UK. The format is:

The Royal Infirmary of Edinburgh [Edinburgh]

As you can see the city the hospital is located in is enclosed in
square
brackets. What I want to do is split it so that the hospital name is
in
column A and the City is in column B

Is there anyway to do this without having to manually go through each
one.

Also the entries are hyperlinks, does this make any difference?

Gord Dibben MS Excel MVP