Replacing commas with carriage return

[Hardip]

Hi All,

I have a list of names like so:

John Thomson, Paul Cooper, Ian Smith, Mark Devlin... and so on

The names are in one continuous string i.e. they start from one cell (A1).

I'd like to replace the commas with a carriage return to place each name
into its own cell. Is there function or calculation I can use to achieve
this?

Any advise would be appreciated.

TIA - H

[Bob Phillips]

=SUBSTITUTE(A21,",",CHAR(10))

but you will still need to manually add wrap text to the cell and adjust its
height

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hardip" <[email protected]> wrote in message
news:[email protected]...
> Hi All,
>
> I have a list of names like so:
>
> John Thomson, Paul Cooper, Ian Smith, Mark Devlin... and so on
>
> The names are in one continuous string i.e. they start from one cell (A1).
>
> I'd like to replace the commas with a carriage return to place each name
> into its own cell. Is there function or calculation I can use to achieve
> this?
>
> Any advise would be appreciated.
>
> TIA - H

[Stefi]

Data->Text to columns/separated by commas
This will place each name into separate columns and it removes commas.
Regards,
Stefi


„Hardip” ezt �*rta:

> Hi All,
>
> I have a list of names like so:
>
> John Thomson, Paul Cooper, Ian Smith, Mark Devlin... and so on
>
> The names are in one continuous string i.e. they start from one cell (A1).
>
> I'd like to replace the commas with a carriage return to place each name
> into its own cell. Is there function or calculation I can use to achieve
> this?
>
> Any advise would be appreciated.
>
> TIA - H

[Bob Phillips]

Oops, I mis-read, this just newlines them.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> =SUBSTITUTE(A21,",",CHAR(10))
>
> but you will still need to manually add wrap text to the cell and adjust
its
> height
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Hardip" <[email protected]> wrote in message
> news:[email protected]...
> > Hi All,
> >
> > I have a list of names like so:
> >
> > John Thomson, Paul Cooper, Ian Smith, Mark Devlin... and so on
> >
> > The names are in one continuous string i.e. they start from one cell
(A1).
> >
> > I'd like to replace the commas with a carriage return to place each name
> > into its own cell. Is there function or calculation I can use to
achieve
> > this?
> >
> > Any advise would be appreciated.
> >
> > TIA - H
>
>

[Hardip]

Hi All,

Thanks for the response. I used the data > text to columns feature,
followed by copy / paste special transpose function.

Thanks again,
H

"Stefi" wrote:

> Data->Text to columns/separated by commas
> This will place each name into separate columns and it removes commas.
> Regards,
> Stefi
>
>
> „Hardip” ezt �*rta:
>
> > Hi All,
> >
> > I have a list of names like so:
> >
> > John Thomson, Paul Cooper, Ian Smith, Mark Devlin... and so on
> >
> > The names are in one continuous string i.e. they start from one cell (A1).
> >
> > I'd like to replace the commas with a carriage return to place each name
> > into its own cell. Is there function or calculation I can use to achieve
> > this?
> >
> > Any advise would be appreciated.
> >
> > TIA - H