Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace pattern of characters
I need replace the contents of a column as follows
The column will have have empty rows rows with 4 characters and rows with 8 characters. For the rows with 8 characters I need to insert a "/' in between. e.g. XXXXWWWW - XXXX/WWWW. I know I can use ???????? in the search, but how do I get the replace to only insert the "/" in between the characters? The first four characters are not always the same. Thank you for your help JFercan. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace pattern of characters
On Wed, 21 Mar 2007 10:44:20 -0700, Fernando
wrote: I need replace the contents of a column as follows The column will have have empty rows rows with 4 characters and rows with 8 characters. For the rows with 8 characters I need to insert a "/' in between. e.g. XXXXWWWW - XXXX/WWWW. I know I can use ???????? in the search, but how do I get the replace to only insert the "/" in between the characters? The first four characters are not always the same. Thank you for your help JFercan. You could use a formula: =IF(LEN(A1)=8,LEFT(A1,4)&"/"&RIGHT(A1,4),A1) Otherwise, you would probably have to use a VBA macro to change the cell contents "in place". --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace pattern of characters
If you're saying you need to put the slant between the 4th & 5th characters
in the 8 character entries, & leave the other rows unaltered, you could try: =IF(A1="","",IF(LEN(A1)=8,LEFT(A1,4)&"/"&RIGHT(A1,4),A1)) -- David Biddulph "Fernando" wrote in message ... I need replace the contents of a column as follows The column will have have empty rows rows with 4 characters and rows with 8 characters. For the rows with 8 characters I need to insert a "/' in between. e.g. XXXXWWWW - XXXX/WWWW. I know I can use ???????? in the search, but how do I get the replace to only insert the "/" in between the characters? The first four characters are not always the same. Thank you for your help JFercan. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace pattern of characters
On Wed, 21 Mar 2007 13:54:16 -0400, Ron Rosenfeld
wrote: On Wed, 21 Mar 2007 10:44:20 -0700, Fernando wrote: I need replace the contents of a column as follows The column will have have empty rows rows with 4 characters and rows with 8 characters. For the rows with 8 characters I need to insert a "/' in between. e.g. XXXXWWWW - XXXX/WWWW. I know I can use ???????? in the search, but how do I get the replace to only insert the "/" in between the characters? The first four characters are not always the same. Thank you for your help JFercan. You could use a formula: =IF(LEN(A1)=8,LEFT(A1,4)&"/"&RIGHT(A1,4),A1) Otherwise, you would probably have to use a VBA macro to change the cell contents "in place". --ron That should be: =IF(A1="","",IF(LEN(A1)=8,LEFT(A1,4)&"/"&RIGHT(A1,4),A1)) --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace pattern of characters
Thank you for your help.
I was hoping for an easier way, but I guess a macro won't be that bad. Thanks for your replies. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replace pattern of characters
On Wed, 21 Mar 2007 13:45:00 -0700, Fernando
wrote: Thank you for your help. I was hoping for an easier way, but I guess a macro won't be that bad. Thanks for your replies. Option Explicit Sub Slash() Dim c As Range For Each c In Selection If Len(c.Text) = 8 Then c.Value = Left(c.Text, 4) & "/" & Right(c.Text, 4) End If Next c End Sub --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace many asterisk characters, '*' in Excel | Excel Discussion (Misc queries) | |||
FIND and REPLACE characters needed | New Users to Excel | |||
FIND and REPLACE characters needed | Excel Worksheet Functions | |||
How do I find replace special characters? | Excel Discussion (Misc queries) | |||
How can I replace 2 of 5 characters within an cell in MS Excel? | Excel Worksheet Functions |