Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcards with substitute of replace
i need to remove the first couple and last few characters of a string.
for example i need to change: TR ROBINSON RD @ SS to ROBINSON RD or TL LOMBARDY WAY @ END to LOMBARDY WAY the first characters before the space could be several different strings, i.e. TL, TR, CROSS etc and same for the last characters in the string. i.e. @ END, @ LITE, (B4 GAS STA) etc i had success with replace and substitute if i knew exactly what the leading and trailing chars were going to be. i can't get either to accept wildcards however. with a street number, street name and zip code, a link to google maps is added to the address what can i do? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcards with substitute of replace
With your string in B5, give this a try...
=LEFT(MID(B5,FIND(" ",B5)+1,99),FIND("@",MID(B5,FIND(" ",B5),99))-2) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Jay Fincannon" wrote in message i need to remove the first couple and last few characters of a string. for example i need to change: TR ROBINSON RD @ SS to ROBINSON RD or TL LOMBARDY WAY @ END to LOMBARDY WAY the first characters before the space could be several different strings, i.e. TL, TR, CROSS etc and same for the last characters in the string. i.e. @ END, @ LITE, (B4 GAS STA) etc i had success with replace and substitute if i knew exactly what the leading and trailing chars were going to be. i can't get either to accept wildcards however. with a street number, street name and zip code, a link to google maps is added to the address what can i do? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcards with substitute of replace
Try something like this:
With text in A1 in the pattern of Random chars followed by a space then the street name followed by a space, an @, and more text. This formula extracts the street name B1: =MID(LEFT(A1,FIND(" @",A1)-1),FIND(" ",A1)+1,255) Does that help? *********** Regards, Ron XL2002, WinXP "Jay Fincannon" wrote: i need to remove the first couple and last few characters of a string. for example i need to change: TR ROBINSON RD @ SS to ROBINSON RD or TL LOMBARDY WAY @ END to LOMBARDY WAY the first characters before the space could be several different strings, i.e. TL, TR, CROSS etc and same for the last characters in the string. i.e. @ END, @ LITE, (B4 GAS STA) etc i had success with replace and substitute if i knew exactly what the leading and trailing chars were going to be. i can't get either to accept wildcards however. with a street number, street name and zip code, a link to google maps is added to the address what can i do? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcards with substitute of replace
hi Jay
i wrote this function and use it alot,- put in a regular module. how to use- to fix ur problem - put this in a free cell: (ur first string in A2, and the second in A3) =split(" ";A2;1;3) returns- ROBINSON RD =split(" ";A3;1;3) returns- LOMBARDY ! why ? becourse there is a double spases betvin LOMBARDY and WAY =split(" ";A3;1;4) returns- LOMBARDY WAY another way to use this function is- say u wana split TR R [OBINSO] N RD @ SS =split("O";A2;1;2) returns- OBINSO another way to use this function is say u want TR ROBINSON but without space =SUBSTITUTE(split(" ";A2;0;2);" ";"") returns- TRROBINSON '--------------------------------------------------------------------- Function Split(Tegn As String, Selle As Range, Fra, Til) Dim lop As Single Dim antal As Single Dim v() ReDim v(Len(Selle.Value)) Application.Volatile For lop = 1 To Len(Selle.Value) If Mid(Selle.Value, lop, 1) = Tegn Then antal = antal + 1 v(antal) = lop End If Next v(0) = 1 If Til = Fra Then Split = Trim(Mid(Selle, v(Fra), 20)) ' v(Til) - v(Fra) + 0)) If Til < Fra Then Split = Trim(Mid(Selle, v(Fra), v(Til) - v(Fra) + 1)) End Function '-------------------------------------------------------- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcards with substitute of replace
obs: u may have to exchange ; (semicolon) with , (commas) in ur fomulas:
=split(" ",A3,1,4) not sure bout that (DK version here) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
wildcards with substitute of replace
sorry to be so late thanking y'all but thank you too much. all works
jay atlanta, ga On Sat, 16 Sep 2006 01:54:01 -0700, excelent wrote: obs: u may have to exchange ; (semicolon) with , (commas) in ur fomulas: =split(" ",A3,1,4) not sure bout that (DK version here) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'Substitute' function with wildcards | Excel Discussion (Misc queries) | |||
Replace/substitute | Excel Programming | |||
Substitute/Replace | Charts and Charting in Excel | |||
Replace or Substitute for COMBIN function | Excel Discussion (Misc queries) | |||
Substitute ,replace and delete in a cell. | Excel Worksheet Functions |