VBA to add and remove text within cells

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 


0
annie.lef (1)
5/24/2006 5:14:28 PM
excel 39879 articles. 2 followers. Follow

2 Replies
410 Views

Similar Articles

[PageSpeed] 5

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(

0
naeyaert (21)
5/29/2006 10:36:12 PM
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.

0
naeyaert (21)
5/29/2006 11:06:50 PM
Reply:

Similar Artilces:

How do I delete a text box from an Excel spreadsheet?
Rightclick on it and select cut If the textbox is from the control toolbox toolbar, you may have to click on the designmode icon first. Uncle Binky wrote: -- Dave Peterson ...

Changing text size
Hi, We enter all our customers in our database in all caps (text) for the names, and addresses. But when you put in the zip code and the database auto finds the town and state it is put in upper, and lower case. Is there a way to change this default to all caps for the auto fill. I am experienced in editing xml. files ( just a note) Any help would be appreciated.....Dennis No, there's no way to change that in the front end, that's something that is happening at display time and is meant to make the data more readable. You could enter a suggestion on Customer Sou...

Changing the Tooltip text in an SDI Toolbar
I've been trying to find a way to change the tootip info for a toolbar. Currently, I've tried getting the toolbar CToolBarCtrl and CToolTipCtrl objects but haven't been able to figure out what to do with them. Any help would be appreciated. Thanks, Ken If you are talking about tooltip info of a particular button on a toolbar, change it in resource editor by double clicking on the button and changing the text that appears at the bottom of the dialog. Text that appears before \n is tootip. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Ken Slight" <kslight@char...

Why does the right side of my paper not print (within margin) Pub
I've had this problem for awhile now, but the current document is a postcard I'm printing 4 to a page with a border around each one. The whole thing prints out just fine except for the right side of the page, which happens to be the whole side of the border. It is well within the margins. I've checked all the settings within Publisher 2003 and my printer program (Dell Photo AIO) that I can find. Thanks! ~Renee'~ Renee'''' wrote: > I've had this problem for awhile now, but the current document is a postcard > I'm printing 4 to a page with a...

importing multiple text files???
Hi, I am experiencing a big problem. As a part of my analysis for PhD I have to analyze more then 1000 files. The data that I have is in text files. To be more precise, I have 5 different data sets, from different meteo centres, each centre has a data set of 365 files for each day during the year. The format of text files is something as following: Camborne Met. Office WINDS rev 4.1 50.20 -5.30 88 02 01 01 00 00 23 0 29 3 35 07:09 (3.0) 07:09 (3.0) 06:08 (3.0) 284 284 65 65 400 400 23 23 12.5 12.5 1 1500 1500 35 35 400 400 43 90.0 43 74.5 313 74.5 HT SPD DIR Radials... 0.101 9.8 113 0.0...

How to see calculation and heading in same cell.
I would like the cell to perform a calculation and then display the answer as will as a heading. In other words the answer and the heading will appear in the same cell. Perhaps you mean something like ="Heading Name: "&A1+B1 ******************* ~Anne Troy www.OfficeArticles.com "Jeracho" <Jeracho@discussions.microsoft.com> wrote in message news:F34BACB9-E2DA-448A-924D-46A475F98F91@microsoft.com... > I would like the cell to perform a calculation and then display the answer as > will as a heading. In other words the answer and the heading will appear in...

Removing non-existent DC from 2000 Domain
Hello, We have a Windows 2000 domain with 2 DC. One of them crashed and we bought a new hardware and Windows 2003 Server to replace it. But I can't in no way to remove the account of the failed DC from AD and when I try to add the new server with the same name as the failed one into the domain, I got an error: "The specified user already exists" Do one knows how can I remove all data, related to the failed DC from AD, so I could add a new DC with the same name? I have already used NTDSUTIL and removed the failed server from the site, but it seems this is not enough. Thanks, G...

How can I add numbers but ignore the minus signs?
How can I add numbers but ignore the minus signs? In the following, the answer I'm after is 17 A1 -10 A2 5 A3 -2 =SUMPRODUCT(ABS(A1:A3)) Gord Dibben MS Excel MVP On Thu, 07 Jun 2007 04:19:52 GMT, Invalid <invalid@invalid.comINVALID> wrote: >How can I add numbers but ignore the minus signs? In the following, the >answer I'm after is 17 > >A1 -10 >A2 5 >A3 -2 > > Gord Dibben <gorddibbATshawDOTca> wrote in news:1o2f63pa84r8o6tdf65sa9ptpbv0nlt7u6@4ax.com: Thank you Gord. ----- > =SUMPRODUCT(ABS(A1:A3)) > > > Gord Dibben M...

How to add summary fields to Group Footer in Access Reports?
How do you add a Summary fields to Group Footers in Access? I have a Detail field I want to Sum in the Group Footer in my report. -- Pat Dools ...

Coping part of a cell content into a seperate cell
Hi I have two cells, one containing first and middle name and another one with surname. I want to combine the first name and surname into a separate cell, can you advise how I can just copy the first name and miss out the middle name please?? Thanks Caz Hi, I assume that the midle name is separated by a space from the first name and is in column A and the last name in column B =TRIM(LEFT(A2,FIND(" ",a2)-1))&" "&B2 "Caz H" wrote: > Hi > I have two cells, one containing first and middle name and another one with &g...

VBA code to hide all the tables on form open
I don't want people to use a blank mdb to import my tables. I manually hide them all. However, after running the macro to delete all records and import from .txt, the table become unhide. I do the importation on daily basis. I posted to macro newsgroup and asked way to hide table after importation action macro but got no answer. Maybe it cannot be done in macro? If so, I need VBA code to hide all the tables on form open. Thanks. Hiding your tables won't prevent people from being able to import them into a blank mdb. All they have to do is ensure that they've set the datab...

emailing spreadsheet within excel
I have a user that has sent her spreadsheet as an attachment using the Send To Function. We use Outlook. Now, it seems as if the Send To option has lost its settings and is now trying to set up an Internet account instead of using the Microsoft Exchange Settings. Anyone know how to reset the Send To settings in Excel? Thanks! ...

generate list of non-entries within dates
I use Excel (2007) to record activity and support for about 100 current online learners and I have to provide an activity summary sheet every two weeks. The list of learners is in one sheet and are marked "current", agreed break" or "completed" in an adjacent column. The total list is about 1000 and grows by about 10 each week. The activity log is another sheet of the same workbook. Assuming that all activity is logged in this way I need to generate a list (sheet) of those current learners who have not submitted any recent work and are therefore "inacti...

Money 2007 add/delete Category
Is there a way to maintain my categories by viewing a list so easily add/delete categories? The Category maintenance in 2007 sucks.. I need to run through every single category just to delete one. Deluxe or Essential? If Deluxe, use the Account List More pull out, Categories & :Payees|Categories should show you all the categories at once and allow you to Move, Modify or Delete them as required. The pulldown at the top lets you see just Categories or Categories and Subcategories. "Vital" <Vital@discussions.microsoft.com> wrote in message news:4D4AAA91-6602-429E-915...

Adding a formula to the same cell (H5) on every tab
I have an inventory spreadsheet with 125 tabs. The tabs are numbered 1 through 125. The are identical except for the data below the column headings. If I wanted to put a formula in H5 on every tab, can it be done other than manually opening every tab and typing it? One additional question: If I add a Summary Tab, how could I show the value of a specific cell on each tab without manually entering it? I show the formula I'm using bring B3 to the summary for every tab: A B 1 Unit Value 2 1 ='1'!B3 3 2 ='2'!B3 4 3 ='3'!B3 5 4 ='4'!B3 6 5 ='5'!B3 7...

VBA type mismatch error
Hello all, The following line is in an Excel function: j = Application.Find(v, Cells(cnt, 1).Value) When watched, v has a value of "s" with type variant/string. cells(cnt,1).value has a value of 2 with a type of variant/double. I assume that the type mismatch occurs because the character "S" can't be found in an integer. How do I get this to work? TIA. Pierre. -- Posted via a free Usenet account from http://www.teranews.com VBA has its own version of =find(). Take a look at VBAs help for InStr. =find() will return an error if there isn't a match. Dim j...

sumproduct--counting--zero--blank cells
I'm using these formula to count, =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) ........etc how do i get it so bank cells are excluded from the count. The way it is now, they are counted in the 0 to 10 range... Thanks Jeremy -- Message posted via http://www.officekb.com COUNTBLANK(range) "jeremy via OfficeKB.com" wrote: > I'm using these formula to count, > > =SUMPRODUCT(($W$9:$W$272>=0)*($W$9:$W$272<10)) > =SUMPRODUCT(($W$9:$W$272>=10)*($W$9:$W$272<20)) > ........etc > how do...

45 Degree Angled Text & Fill Option
I have Excel 2003 (11.6355.6360) running on XP and I'm wondering if this is a bug or not. If you have text in the first Row and you set it to 45 degrees angled, if you try and fill different cells with different fill colors, sometimes the box will fill angled, othertimes straight up and down. As a simple test, try creating a blank worksheet and make the first 3 columns 35 each in width and 100 in height. The type some text in each of the cells - not too much. Now select all 3 cells and format their alignment to 45 degrees. Now pick each one of the cells and fill each with a different ...

Add Company Holidays To Calendars
Hello, I'm trying to find a simple way to automatically add all company holidays to everyone's calendar in my organization. We would prefer to stay away from sending meeting invites to everyone as this becomes overhead when people join the company and when they leave. I have investigated using the Outlook.HOL file and asking everyone to import the custom holidays that we add to that file, but that involves a lot of overhead as well. Is there a way to set this up within Exchange to automatically populate certain events on peoples calendars? Thank you. -- Jason Gay, MCSE, CCNA Tec...

References omit formatting and return cell address
In two cases of references between worksheets, the formatting from the original cell does not appear in the cell that it is referenced to. Case 1: Worksheet 1, A1 contains a currency formatted number - $2,000 Worksheet 2, A1 references the Workhseet 1, A1 cell using the = sign, yet it returns 2000 (unless I manually reformat the Workksheet 2 cell to Currency Case 2: Worksheet 3, A1 contains an apartment # - e.g. 4 Worksheet 4, A1 references this cell but returns the cell address - Worksheet2,!A1' - rather than the number 4. I tried different formats for the number 4,...

Cannot add new account to Money 2006
Hi all, I've bought Money 2006 std at last week, and first I've tried to import my Money 2000 file. The Money 2006 said that the file is not importable. OK. I've tried to create new file, which was successfully created, but I cannot add any new account. I've always got the same error message: The operation cannot be performed. Details: Product: Money ID: obres:34 Source: 15.0 Version: 15.0 Symbolic name: errUnknown Message: This operation cannot be performed. Reason Usually caused by a corrupt file. User Operation Run the Money File Repair tool to fix th...

How can I insert a cell reference in a footer (eg for variable foo
Any ideas on how to do this? I'm trying to create a template with the doc reference number in the footer However, I'm trying to avoid users having to edit the footer (because this just wont get done). Hi only possible with VBA using an event procedure. e.g. put the following code in your workbook module for cell A1 Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In Me.Worksheets With wkSht.PageSetup .CenterFooter = wksht.range("A1").value End With Next wkSht End Sub -- Regards Frank Kabel Frankfurt, Ger...

Text in pivot table limited to 255 characters
I am using a pivot table as an efficient way to aggregate text responses from a large data set. However, the pivot table cuts off the text after the first 255 characters (similar to when you copy a worksheet by using the move/copy option). How can I overcome this? I have tried putting the pivot table on the same sheet as the dataset, but that does not work. I should also note that these pivot tables are then fed into an automated report through a complicated set of VLOOKUPs, etc. The pivot table aggregates several questions and responses from many areas of the datset into one discr...

Word Object Border Remove?
I've inserted a Word Document Object into my spreadsheet and would like to remove the border that appears around it for printing? Any help would be appreciated. Thanks! Right-click on the Object in Excel. Format Object>Colors and Line>Color.....No Color. Note: you can also set the transparency and other options. Gord Dibben Excel MVP On Thu, 20 Jan 2005 13:56:44 -0800, "TT" <noreply@hotmail.com> wrote: >I've inserted a Word Document Object into my spreadsheet and would like to >remove the border that appears around it for printing? Any help...

How do I chart the same data cell on a range of worksheets?
I have the same row of cells on numerous worksheets that I want to chart or consolidate onto another worksheet ? Keith - You need to create a consolidated data range: http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Keith wrote: > I have the same row of cells on numerous worksheets that I want to chart or > consolidate onto another worksheet ? ...