Splitting the contents of a cell
 

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?

Splitting the contents of a cell
 

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

Splitting the contents of a cell
 

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?

Splitting the contents of a cell
 

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?

Splitting the contents of a cell
 

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?

Splitting the contents of a cell
 

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?

Splitting the contents of a cell
 

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?

Splitting the contents of a cell
 

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