Prev: Excel VBA 'Help'
Next: Rename Cell
From: mrmnz on 22 Apr 2010 21:01
Hi

I have a column of codes and some are written as below:

A2ABC
A2BBB
A2CBC
A2DBD

I want them all to has a space after the A2 ie. A2 ABC, A2 BBB etc etc.

What is the best/easiest way to do this?

Thanks.
From: FSt1 on 22 Apr 2010 21:21
hi
use a helper column off to the side. use this formula
=LEFT(A1,2)&" "&MID(A1,3,99)
adjust cell reference to fit your data.
copy down as far as needed.
copy the helper column and paste special values.
you can then replace the old data with the new.
regards
FSt1

"mrmnz" wrote:

> Hi
>
> I have a column of codes and some are written as below:
>
> A2ABC
> A2BBB
> A2CBC
> A2DBD
>
> I want them all to has a space after the A2 ie. A2 ABC, A2 BBB etc etc.
>
> What is the best/easiest way to do this?
>
> Thanks.
From: Ron Rosenfeld on 22 Apr 2010 21:26
On Thu, 22 Apr 2010 18:01:01 -0700, mrmnz <mrmnz(a)discussions.microsoft.com>
wrote:

>Hi
>
>I have a column of codes and some are written as below:
>
>A2ABC
>A2BBB
>A2CBC
>A2DBD
>
>I want them all to has a space after the A2 ie. A2 ABC, A2 BBB etc etc.
>
>What is the best/easiest way to do this?
>
>Thanks.

You could use Find/Replace

Select the cells that start with A2
Find/Replace
Find what: A2
Replace with: A2<space>

You could use a formula:

=SUBSTITUTE(A1,"A2","A2 ",1)

On the other hand, if you always want to insert a <space> between the 2nd and
3rd characters, you could use:

=REPLACE(A1,3,0," ")

And if you always want to insert a <space> after the first digit, which might
or might not be in the second position:

=REPLACE(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4,5,6,7,8,9,0"))+1,0," ")

--ron
From: mrmnz on 22 Apr 2010 22:41
Thank you so much - it's worked just great.

"Ron Rosenfeld" wrote:

> On Thu, 22 Apr 2010 18:01:01 -0700, mrmnz <mrmnz(a)discussions.microsoft.com>
> wrote:
>
> >Hi
> >
> >I have a column of codes and some are written as below:
> >
> >A2ABC
> >A2BBB
> >A2CBC
> >A2DBD
> >
> >I want them all to has a space after the A2 ie. A2 ABC, A2 BBB etc etc.
> >
> >What is the best/easiest way to do this?
> >
> >Thanks.
>
> You could use Find/Replace
>
> Select the cells that start with A2
> Find/Replace
> Find what: A2
> Replace with: A2<space>
>
> You could use a formula:
>
> =SUBSTITUTE(A1,"A2","A2 ",1)
>
> On the other hand, if you always want to insert a <space> between the 2nd and
> 3rd characters, you could use:
>
> =REPLACE(A1,3,0," ")
>
> And if you always want to insert a <space> after the first digit, which might
> or might not be in the second position:
>
> =REPLACE(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&"1,2,3,4,5,6,7,8,9,0"))+1,0," ")
>
> --ron
> .
>
From: Ron Rosenfeld on 22 Apr 2010 23:40
On Thu, 22 Apr 2010 19:41:01 -0700, mrmnz <mrmnz(a)discussions.microsoft.com>
wrote:

>Thank you so much - it's worked just great.

Glad to help.
--ron
 | 
Pages: 1
Prev: Excel VBA 'Help'
Next: Rename Cell