Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Find nth occurrence and replace with ":"
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Try this: =REPLACE(A1,FIND(" ",A1,1),1,": ") Does this work for you? -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=473524 |
#4
|
|||
|
|||
=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 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! |
#5
|
|||
|
|||
Ah, I see, I was supposed to use REPLACE with FIND. Thank you so much! This is perfect! swatsp0p Wrote: Try this: =REPLACE(A1,FIND(" ",A1,1),1,": ") Does this work for you? -- marlea ------------------------------------------------------------------------ marlea's Profile: http://www.excelforum.com/member.php...o&userid=26209 View this thread: http://www.excelforum.com/showthread...hreadid=473524 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace nth place with colon | Excel Worksheet Functions | |||
find position of a number in a string | Excel Worksheet Functions | |||
Search - replace NOT in formulas | Excel Worksheet Functions |