wildcards with substitute of replace

[Jay Fincannon]

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?

 

[Jim Cone]

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"
<[email protected]>
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?

 

[Ron Coderre]

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

 

[excelent]

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
'--------------------------------------------------------

 

[excelent]

obs: u may have to exchange ; (semicolon) with , (commas) in ur fomulas:
=split(" ",A3,1,4)
not sure bout that (DK version here)

 

[Jay Fincannon]

sorry to be so late thanking y'all but thank you too much. all works

jay atlanta, ga