Left Function and Text (2000 SR-1)

Topic

New Reply

I have to separate cities/towns from the post code and put them in separate columns for a huge mail merge, (around 1,000 addresses). There is something wrong in the formula I am using. Can any lounger help me out.

This information came originally from another program which dumped the info into Excel.

I have attached a spreadsheet with an example of the formula and the weird results that I am getting.

Thanks in advance

Reply | Quote

Replies

Hi Hetty,
You can change your formula in column D to something like:

=TRIM(MID(A1,B1+1,LEN(A1)))

Your original RIGHT() formula counts from the right, not the left, which is why your formula was not working properly. I added the TRIM function as at least one of your entries has a double space between the Town and Postcode.
Hope that helps.

Reply | Quote

Rory

You are the

Thank you so much. You’re message came through just as I was threatening to strangle the person who dumped this mess on my desk.

Reply | Quote

Rory

You are the

Thank you so much. You’re message came through just as I was threatening to strangle the person who dumped this mess on my desk.

Reply | Quote

Hi Hetty,
You can change your formula in column D to something like:

=TRIM(MID(A1,B1+1,LEN(A1)))

Your original RIGHT() formula counts from the right, not the left, which is why your formula was not working properly. I added the TRIM function as at least one of your entries has a double space between the Town and Postcode.
Hope that helps.

Reply | Quote