Find nth occurrence and replace with ":"

[marlea]

I am altering REPLACE functions for different parts of my data. This works OK, but it would be more efficient if I could apply one function to all of my data.

Data:
Column A
R1 volume measured by...
R4 mass times...
R11 weight is related...
R12 height
R100 distance and time
R124 force

I want to place a colon after the numbers; e.g., R1: volume...; R12: height. If I knew how to find the first occurrence of a space, I think I could just find those spaces and replace them with a colon and a space. My work-around is to use REPLACE this way:

I apply this formula to cells with one-digit numbers:
=REPLACE(A1, 3, 1, ": ")

Then I slightly change the formula for cells with two-digit numbers:
=REPLACE(A1, 4, 1, ": ") ... and so on.

Can someone show me a better way of doing this? Thank you!

[swatsp0p]

Try this:

=REPLACE(A1,FIND(" ",A1,1),1,": ")

Does this work for you?

[B. R.Ramachandran]

Hi,

Use,
=REPLACE(A1,FIND(" ",A1),1,": ")

Regards,
B. R. Ramachandran


"marlea" wrote:

>
> I am altering REPLACE functions for different parts of my data. This
> works OK, but it would be more efficient if I could apply one function
> to all of my data.
>
> Data:
> Column A
> R1 volume measured by...
> R4 mass times...
> R11 weight is related...
> R12 height
> R100 distance and time
> R124 force
>
> I want to place a colon after the numbers; e.g., R1: volume...; R12:
> height. If I knew how to find the first occurrence of a space, I think
> I could just find those spaces and replace them with a colon and a
> space. My work-around is to use REPLACE this way:
>
> I apply this formula to cells with one-digit numbers:
> =REPLACE(A1, 3, 1, ": ")
>
> Then I slightly change the formula for cells with two-digit numbers:
> =REPLACE(A1, 4, 1, ": ") ... and so on.
>
> Can someone show me a better way of doing this? Thank you!
>
>
> --
> marlea
> ------------------------------------------------------------------------
> marlea's Profile: http://www.excelforum.com/member.php...o&userid=26209
> View this thread: http://www.excelforum.com/showthread...hreadid=473524
>
>

[Myrna Larson]

=SUBSTITUTE(A1," ",": ")

should work for numbers of any length and replace all spaces with colon and
space.


On Wed, 5 Oct 2005 13:54:07 -0500, marlea
<[email protected]> wrote:

>
>I am altering REPLACE functions for different parts of my data. This
>works OK, but it would be more efficient if I could apply one function
>to all of my data.
>
>Data:
>Column A
>R1 volume measured by...
>R4 mass times...
>R11 weight is related...
>R12 height
>R100 distance and time
>R124 force
>
>I want to place a colon after the numbers; e.g., R1: volume...; R12:
>height. If I knew how to find the first occurrence of a space, I think
>I could just find those spaces and replace them with a colon and a
>space. My work-around is to use REPLACE this way:
>
>I apply this formula to cells with one-digit numbers:
>=REPLACE(A1, 3, 1, ": ")
>
>Then I slightly change the formula for cells with two-digit numbers:
>=REPLACE(A1, 4, 1, ": ") ... and so on.
>
>Can someone show me a better way of doing this? Thank you!

[marlea]

Ah, I see, I was supposed to use REPLACE with FIND. Thank you so much! This is perfect!

Try this:

=REPLACE(A1,FIND(" ",A1,1),1,": ")

Does this work for you?