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?

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



Keith wrote:

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

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)



"Keith" wrote:

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?

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



"Keith" wrote:

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?

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

"tim m" wrote in message
...
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)



"Keith" wrote:

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?

Strange doings Howard.

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


Gord Dibben MS Excel MVP

On Wed, 20 Sep 2006 13:46:57 -0700, "L. Howard Kittle"
wrote:

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

"tim m" wrote in message
...
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)



"Keith" wrote:

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?

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Strange doings Howard.

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


Gord Dibben MS Excel MVP

On Wed, 20 Sep 2006 13:46:57 -0700, "L. Howard Kittle"

wrote:

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

"tim m" wrote in message
...
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)



"Keith" wrote:

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?

Thanks for the update.

JOOTT


Gord

On Thu, 21 Sep 2006 09:22:28 -0700, "L. Howard Kittle"
wrote:

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Strange doings Howard.

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


Gord Dibben MS Excel MVP

On Wed, 20 Sep 2006 13:46:57 -0700, "L. Howard Kittle"

wrote:

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

"tim m" wrote in message
...
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)



"Keith" wrote:

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