VBA to add and remove text within cells

A

Annie

Hi, I have a field named "Postal" at the top of column F that always include
a number with 5 digits then a city name then a region name, such as "11090
CARCASSONNE Linguadoca-Rossiglione". I need to create a program to have this
field changed as following : "F-11090", then copying "CARCASSONNE" into the
City field which is empty (column G). The city name is always starting just
one space character after the postcode, same thing for the region name, it
always starts one space character after the city name. The region has to be
removed completely.
Also, I have a field named Phone (column E), it is always starting with
"+33", and I need to remove the "+33" automatically while keeping the
following numbers in the fields.

Maybe someone could help me ?
Thanks very much
Annie
 
A

aresen

First of all, the 5-digit field is easy. =Left(F1,5) will get you that.
Next the city name. You can search for a space following the beginning
of the city name region. Since the field is 5 characters long followed
by a space, you can use =FIND(" ",F1,7) to find the character position
of the first space after the sixth character. This is your first
problem. What if the city is New York? Minneapolis St. Paul? You'll
need to put a known delimiter between fields i.e, "12345;Minneapolis
St. Paul;LastPlace on Earth to Visit in Winter". Her I'm using a
semicolon instead of a space character. This is easy to locate and
parse. I'm now going to assume you will use a semmicolon to delimit the
fields.
Next thing is that you don't want to monkey around with the contents of
column F since that's the source of your information.
To keep it from looking too complex, let's use a couple throw-away
columns. I'll use Y and Z just to keep them out of the way. I'm going
to use { and } instead of quote and un-qoute tokeep it clear (as mud)
which you are not to type into the cells. In column Y enter the formula
{=FIND(";",F1)}.
This will locate the position of the first semicolon. Next, in column Z
enter the formula {=FIND(";",F1,Z1+1)}. This locates the position of
the second semicolon.
Now you can put the parsed information wherever you like. For example,
if you want the city in column G then in column G enter the formula
"=MID(F1,Y1+1,Z1-Y1-1)". This will place the city name in column G
staring at one character position after the first semicolon for a
length equal to the difference between the two positions less 1.
Now, in column H enter the formula "=MID(F1,Z1+1,999)". This doesn't
bother to determine the length of the field but does assume it's less
than a thousand characters.
That will be the region information.
Let's put the "field" name in column I by entering the formula
"="F-"&LEFT(F1,Y1-1)"
Therefore, let's put the region in column H using the formula
"=LEFT(F1,FIND(";",F1)-1)". That way, the field can be of any length 5
+ or -.
Since column G is to contain the city field, we need to extract the
city. Use the following formula (even though complex):
"=MID(F1,Y1+1,Z1-Y1-1)"
Now, all fields are extracted into deparate columns while preserving
the original input data (column F).
Now for the phone number, let's use column I. Enter the formula
"=SUBSTITUTE(
 
A

aresen

Excuse me, I just hit the wrong icon and lost a billion hours of reply.
I'll assume your using no further than column Z in your worksheet so
that I can use AA, AB and AC.
First off, I'll not "destroy" the source information by overwriting
columns E & F and, instead, will use column H to edit the first field.
Column G will contain the city, column I will contain the region and
column J will contain the phone number.
I will use { and } to delimit the formulas. Do not enter these
characters.
In column K, enter the formula {=FIND(";",F1)}. This will locate the
first ";" delimiter. Then in column L enter the formula
{=FIND(";",F1,K1+1)}. This locates the second delimiter.
Since you're using column G for the city, enter the formula
{=MID(F1,K1+1,L1-K1-1)}.
Let's use column H for the region with the formula {=MID(F1,L1+1,999)}.
We're looking atthe end of cell F1 starting one character after the
second semicolon for the next 999 characters. I've assumed the region
will be less than a thousand characters long.
Column I will be used for the original region using the formula
{="F-"&LEFT(F1,K1-1)}.
Column G (the city) needs the formula {=MID(F1,K1+1,L1-K1-1)}.
Lastly, Column J needs the formula {=SUBSTITUTE(E1,"+33","")}.
The bottom line is that your first field (starting with "F-" is now in
column I; the city is in column G; the region is in column H; aaand the
phone number is in column J.

The above does everything without macros. It can also be performed in a
macro but, again, we ddon't want to disturb the source information. I
don't want to continnue stripping of the first 5 characters as a field.

I'm not sure this helps. Continue this correspondence if it doesn't.
 
Top