Trim(Left( -- what if no spaces?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using the following which is working very well except it not working if
there are no spaces in the text of the field. Can someone help?

MAILING_ADDRESS_2: Trim(Left([Mailing_Address2],InStr(1,[mAILING_aDDRESS2],"
"))) & " " &
(Right(Trim([mailing_address2]),Len(Trim([mailing_address2]))-InStr(1,[mailing_address2]," ")))

Thank you.
 
Try


MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0
,[Mailing_Address2],Trim(Left([Mailing_Address2],InStr(1, mAILING_aDDRESS2],"
"))) & " " &
(Right(Trim([mailing_address2]),Len(Trim([mailing_address2]))-InStr(1,[mailing_address2]," "))))
 
Thanks Ofer.

Unfortunately, I keep getting an Invalid Syntax message when I try to click
out of this field after pasting.

Can you add what's missing???

Maria

Ofer Cohen said:
Try


MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0
,[Mailing_Address2],Trim(Left([Mailing_Address2],InStr(1, mAILING_aDDRESS2],"
"))) & " " &
(Right(Trim([mailing_address2]),Len(Trim([mailing_address2]))-InStr(1,[mailing_address2]," "))))
--
Good Luck
BS"D


Maria said:
I'm using the following which is working very well except it not working if
there are no spaces in the text of the field. Can someone help?

MAILING_ADDRESS_2: Trim(Left([Mailing_Address2],InStr(1,[mAILING_aDDRESS2],"
"))) & " " &
(Right(Trim([mailing_address2]),Len(Trim([mailing_address2]))-InStr(1,[mailing_address2]," ")))

Thank you.
Click to expand...
Click to expand...
 
MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0
,[Mailing_Address2]
,Trim(Left([Mailing_Address2],InStr(1,[Mailing_Address2]," ")))
& " " &
Trim(Mid([mailing_address2],Instr(1,[Mailing_Address2]," ")+1)) )

IF this gives you trouble, then try breaking this down into steps
IIf(Instr([Mailing_Address2]," ")=0,[Mailing_Address2], "Has Space")
If that works, then add to it
MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0 ,[Mailing_Address2]
,Trim(Left([Mailing_Address2],InStr(1,[Mailing_Address2]," "))))
If that works, then add a bit more.

--
Maria said:
Thanks Ofer.

Unfortunately, I keep getting an Invalid Syntax message when I try to
click
out of this field after pasting.

Can you add what's missing???

Maria

Ofer Cohen said:
Try


MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0
,[Mailing_Address2],Trim(Left([Mailing_Address2],InStr(1,
mAILING_aDDRESS2],"
"))) & " " &
(Right(Trim([mailing_address2]),Len(Trim([mailing_address2]))-InStr(1,[mailing_address2],"
"))))
--
Good Luck
BS"D


Maria said:
I'm using the following which is working very well except it not
working if
there are no spaces in the text of the field. Can someone help?

MAILING_ADDRESS_2:
Trim(Left([Mailing_Address2],InStr(1,[mAILING_aDDRESS2],"
"))) & " " &
(Right(Trim([mailing_address2]),Len(Trim([mailing_address2]))-InStr(1,[mailing_address2],"
")))

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Worked great! Thank you so much!

Maria

John Spencer said:
MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0
,[Mailing_Address2]
,Trim(Left([Mailing_Address2],InStr(1,[Mailing_Address2]," ")))
& " " &
Trim(Mid([mailing_address2],Instr(1,[Mailing_Address2]," ")+1)) )

IF this gives you trouble, then try breaking this down into steps
IIf(Instr([Mailing_Address2]," ")=0,[Mailing_Address2], "Has Space")
If that works, then add to it
MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0 ,[Mailing_Address2]
,Trim(Left([Mailing_Address2],InStr(1,[Mailing_Address2]," "))))
If that works, then add a bit more.

--
Maria said:
Thanks Ofer.

Unfortunately, I keep getting an Invalid Syntax message when I try to
click
out of this field after pasting.

Can you add what's missing???

Maria

Ofer Cohen said:
Try


MAILING_ADDRESS_2: IIf(Instr([Mailing_Address2]," ")=0
,[Mailing_Address2],Trim(Left([Mailing_Address2],InStr(1,
mAILING_aDDRESS2],"
"))) & " " &
(Right(Trim([mailing_address2]),Len(Trim([mailing_address2]))-InStr(1,[mailing_address2],"
"))))
--
Good Luck
BS"D


:

I'm using the following which is working very well except it not
working if
there are no spaces in the text of the field. Can someone help?

MAILING_ADDRESS_2:
Trim(Left([Mailing_Address2],InStr(1,[mAILING_aDDRESS2],"
"))) & " " &
(Right(Trim([mailing_address2]),Len(Trim([mailing_address2]))-InStr(1,[mailing_address2],"
")))

Thank you.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top