How to split cell based on capitalised suburb name

Hi 

I have data such as the following in a single cell per line

Level 8, 160 Marsden St, PARRAMATTA
15 Carter Street, HOMEBUSH BAY
223 - 239 Liverpool Road, ASHFIELD

I want to put the Suburb name into a seperate cell without going through 
hundreds of rows of data manually.  There can be spaces in the suburb name 
and multiple commas in the field.  The only way to differentiate the suburb 
name is that it is in UPPERCASE.  Does anyone know a formula or macro that 
could complete this?

Thanks
0
Utf
11/16/2009 6:09:01 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1366 Views

Similar Articles

[PageSpeed] 27

Check out whether the below would help. The below will extract the 
information after the last comma.

=TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",255),LEN(A1)-
LEN(SUBSTITUTE(A1,",",))),255))

If this post helps click Yes
---------------
Jacob Skaria


"Bentam3" wrote:

> Hi 
> 
> I have data such as the following in a single cell per line
> 
> Level 8, 160 Marsden St, PARRAMATTA
> 15 Carter Street, HOMEBUSH BAY
> 223 - 239 Liverpool Road, ASHFIELD
> 
> I want to put the Suburb name into a seperate cell without going through 
> hundreds of rows of data manually.  There can be spaces in the suburb name 
> and multiple commas in the field.  The only way to differentiate the suburb 
> name is that it is in UPPERCASE.  Does anyone know a formula or macro that 
> could complete this?
> 
> Thanks
0
Utf
11/16/2009 6:15:01 AM
Perfect.  Thank you.  Now I just go back and break down why it works

"Jacob Skaria" wrote:

> Check out whether the below would help. The below will extract the 
> information after the last comma.
> 
> =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",255),LEN(A1)-
> LEN(SUBSTITUTE(A1,",",))),255))
> 
> If this post helps click Yes
> ---------------
> Jacob Skaria
> 
> 
> "Bentam3" wrote:
> 
> > Hi 
> > 
> > I have data such as the following in a single cell per line
> > 
> > Level 8, 160 Marsden St, PARRAMATTA
> > 15 Carter Street, HOMEBUSH BAY
> > 223 - 239 Liverpool Road, ASHFIELD
> > 
> > I want to put the Suburb name into a seperate cell without going through 
> > hundreds of rows of data manually.  There can be spaces in the suburb name 
> > and multiple commas in the field.  The only way to differentiate the suburb 
> > name is that it is in UPPERCASE.  Does anyone know a formula or macro that 
> > could complete this?
> > 
> > Thanks
0
Utf
11/16/2009 6:29:02 AM
Here we are substituting the last instance of comma with 255 spaces and then 
getting the trimmed string from the right.

To find the number of commas in the text string we have used
=LEN(A1)-LEN(SUBSTITUTE(A1,",",))

If this post helps click Yes
---------------
Jacob Skaria


"Bentam3" wrote:

> Perfect.  Thank you.  Now I just go back and break down why it works
> 
> "Jacob Skaria" wrote:
> 
> > Check out whether the below would help. The below will extract the 
> > information after the last comma.
> > 
> > =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",255),LEN(A1)-
> > LEN(SUBSTITUTE(A1,",",))),255))
> > 
> > If this post helps click Yes
> > ---------------
> > Jacob Skaria
> > 
> > 
> > "Bentam3" wrote:
> > 
> > > Hi 
> > > 
> > > I have data such as the following in a single cell per line
> > > 
> > > Level 8, 160 Marsden St, PARRAMATTA
> > > 15 Carter Street, HOMEBUSH BAY
> > > 223 - 239 Liverpool Road, ASHFIELD
> > > 
> > > I want to put the Suburb name into a seperate cell without going through 
> > > hundreds of rows of data manually.  There can be spaces in the suburb name 
> > > and multiple commas in the field.  The only way to differentiate the suburb 
> > > name is that it is in UPPERCASE.  Does anyone know a formula or macro that 
> > > could complete this?
> > > 
> > > Thanks
0
Utf
11/16/2009 6:45:02 AM
Reply:

Similar Artilces:

Exchange 2003 Forms Based Authentication
Has anybody implemented FBA on Exchange 2003 with ISA 2004? I need FBA turned on in Exchange 2003 but not turned on at the ISA level. Any help would be appreciated. On Mon, 11 Oct 2004 08:14:33 -0700, "Mike L" <anonymous@discussions.microsoft.com> wrote: >Has anybody implemented FBA on Exchange 2003 with ISA >2004? I need FBA turned on in Exchange 2003 but not >turned on at the ISA level. Any help would be appreciated. Indeed. This: http://www.isaserver.org/tutorials/2004pubowamobile.html Gives you what you need and also an insight on some other stuff that you c...

OR cell range
Hi, I have a formula that works fine to produce a result of either Pass or Fail in a cell. This formula looks at 4 cells thus: =IF(OR(J133="Fail",J134="Fail",J135="Fail",J136="Fail"),"Fail","Pass") I now have a requirement where this will cover more than 20 cells in a column and rather than write a condition for each cell is there some other way to do this. I tried naming a range of cells but that doesn't work. Many thanks Hi =IF(COUNTIF(J133:J153,"Fail"),"Fail","Pass") -- Regards R...

Can't move from cell to cell
I'm trying to move from cell to cell with my arrow buttons but it doesn't let me. It appears the highlighted cell is locked and the only way I can move from cell to cell is with my mouse pointer. My arrow buttons do work because I tried it in other programs. Any help will be appreciated. Ann, Looks like you have Scroll Lock turned on. Turn it off with the key on = the top right of the keyboard. HTH Anders Silv=E9n "ann smith" <compcoff@hotmail.com> skrev i meddelandet = news:e6LPf$%23oDHA.2424@TK2MSFTNGP10.phx.gbl... > I'm trying to move from cell to ce...

how to highlight cells ?
I have a spreadsheet that consists of 100 columns with 100 rows; How can I highlight a cell whose value exceeds 500? tia From menu select Format|Conditional formatting Set Condition Cells value is greater than 500. Select the format you would need. Click OK If this post helps click Yes -------------- Jacob Skaria "Sul@MS" wrote: > I have a spreadsheet that consists of 100 columns with 100 rows; > > How can I highlight a cell whose value exceeds 500? tia > Please review this article http://www.contextures.com/xlcondFormat01.html If this post helps click Yes -...

Macro do delete values in cells
If I tipe a value (text) in N I want the values in the same row in O P & A to be deleted. Please help ...

interpolating blank cells
I want to graph the data from a table, but I want the charts to interpolate the lines inbetween the "blank" cells. All of the cells have formulas in them because they are pulling the data from another worksheet, but some of them appear blank because there is no data for that time slot. I have tried the Tools:Options:Charts: Interpolate blank cells, but I can't get it to work. Please help. Thanks. You see a cell with a formula is not blank Replace you formula by =IF(your-formula="",NA(), your_formula) Such as =IF(Sheet1A1="",NA(),Sheet!A1) best wishes ...

Insert random number only if cell empty
Hello. I'd like my spreadsheet to place a 7 digit random number into a cell only the cell is empty, otherwise leave the contents as is. I simply don't want the number to change if one is already present. I currently keep the number static by pressing F9 when I enter the RAND formula, but that forces me to manually change the number when I save the spreadsheet to another name. I'd like the spreadsheet to automatically calculate a new number in the cell as soon as I erase the previous number in that cell. Any help on this issue will be appreciated. Thanks. "M.L.&qu...

Cell range expand or contract
I inherited an Excel workbook that pulls a great deal of its data from an Access db using MSQuery. Some of the worksheets contain bar charts that get their source data from the information pulled in from Access. I am having trouble finding a way to make the cell ranges that hold the chart source data expand or contract depending on the amount of data. For example, one month the source for chart A may be Data! A1:B5. The next month there may have been more transactions and the range would be Data!A1:B10. Right now the user goes in and manually adjusts the cell ranges. Is there some way to m...

Cell.Find in VBA
Hi, I have the following VBA Macro: Set FoundCell = .Cells.Find(What:="199", _ After:=.Cells(1), LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) I don't how to write a VBA macro that would cells.find all 5-digit numbers. Here's an example: Column A 199 199 75781 75899 199 80012 Thanks, Hi I think you have to loop through all cells and check the length/value of each cell -- Regards Frank Kabel Frankfurt, Germany "Jeff" <Jeff@discussions.microsoft.com> schrieb im Newsbeitrag news:D3CEA329-2A...

Considering cells colored via conditional formatting
EXCEL 2007 Any piece of code which, for example, select or count the cells which are colored yellow, amongst the currently selected range, because of meeting ANY criteria of CONDITIONAL FORMATTING. In other words, considering cells which are not actually possessing yellow as an interior color but being displayed so because of CONDITIONAL FORMATTING. -- Thanx in advance, Best Regards, Faraz There are two pages at Chip Pearson's wepage that you need to look at. the 2nd gives a detailed example of how to do what you want. 'Conditional Formatting' (http:/...

Can I split a cell diagonally, with text in each triangle ?
I am putting together a calendar on excel and would like to split a cell diagnolly, colour each section and be able to type text in each triangle. Is this possible and if so how ? Many thanks Helen No it is not possible! (Now that I have said that someone will probably come in with a method) Youi could, however, make two triangles with different fills, and put text in them or make the fills partially tranparent and put text in the cells under them "Helen T" wrote: > I am putting together a calendar on excel and would like to split a cell > diagnolly, colour each ...

auto email if cell value changed
I'm using a workbook to schedule staff. Each month is a worksheet then I have some counts that are done. The days of the month are in the top row and the names of the staff in the first column. We've set up autorefresh on web pages so the schedule is always available but... people still don't notice shift changes. I want excel to generate an email to the person (we're running exchange too) if a cell value is changed. I was thinking of a couple of steps. 1. So they only get emails about the current month and the month ahead -- Have a cell at the top with YES/NO in i...

Progress bar based on percentage
I have implemented a Progress bar explained on this article: http://support.microsoft.com/kb/283030. However, this is still not what I am looking for, what I need is a Progress bar just like this one, but based on percentage field (bound Form with a percentage field). Say, the percentage field is 80%, have the progress bar show as such (80% progressed). Anyone help, tip comments is much appreciated. Adnan Have you taken a look at http://www.rogersaccesslibrary.com/OtherLibraries.asp Specifically, Lambert Heenan's 'ProgressBar.mdb (38 KB) Access 97, 2000, XP'. In vba yo...

VBA to count periods in a single cell
I am looking to count the number of periods within a single cell using vba. For instance if I have 4 cells that contain the following: 5M09-0000700.01.10 5M09-0000700.01.10.10 5M09-0000700.01.10.10.10 5M09-0000700.01.10.10.20 Thus the output of the code for the first cell should be '2', the second cell output would be '3', and the third/fourht cells would be '4' because that many period characters were encountered. Data within these cells will contain letters, numbers, periods, a few spaces and the "-" dash symbol. Any help is greatly appre...

Need to verify cell location before running a macro, how?
I am requesting users to click on a cell in column B (in which they are actually selecting a row of reference information), then to click on an icon which launches a macro that runs relevant to the row selected in column B. I would like Excel to verify that the selected cell is actually in column B prior to running the macro. If a cell in column B is not selected, I want to present a pop up message reminding them to select a cell in column B. Please help! If ActiveCell.Column <> 2 Then MsgBox "Don't Do That" Exit Sub End If -- Jim Cone Portland, Oregon USA http:...

Selecting a named range, the name of the named range is in a cell
What i want to do is the following : I have a cell which i have named "ExtraRisk". This cell contains text : example "Electricity", which is also the name of a range. Now i want to select this named range "Electricity" & copy the range How do i do this. What i have so far is : Sub Risicos_toevoegen() ..... Sheets("MASTER").Select 'I'm stuck here.... Application.Goto Reference:="ExtraRisk" 'I do not want to copy the named range "ExtraRisk", but i want to copy the named range where the va...

Named pipe problem
I followed Microsoft's Overlapped sample to create a namedpipe server, but in WaitForMultipleObjects, the first event always occured. It makes me couldn't to send message to other named pipe clients. DWORD dwWait, cbBytes, dwErr; BOOL fSuccess; LPTSTR lpszPipename = _T("\\\\.\\pipe\\eventpipe"); for (int j = 0; j < MAX_PIPECLIENTS; j++) { // Create an event instances m_log.WriteMessage(_T("Create Events")); m_hPipeEvents[j] = CreateEvent( NULL, // no security attribute TRUE, // manual-reset event FALSE, // initial state = signa...

Change display name #2
Hello, Currently we're hosting 4 domains. To keep everything organized I've added the location name to the users' display name. Example: LOC1_Bob Smith, LOC2_Tom Smith. This is something we want to show in the users' To: box when using outlook. The drawback is when emails are sent to the "outside" those recipients see that display name too. Is there another/better way of doing this? Thank you, Chris Pirillo <chrisp516@gmail.com> wrote: > Hello, > > > > Currently we're hosting 4 domains. To keep everything organized I've > ...

can't resolve user names
i've just added exchange 2003 to a small business server 2003 network. The users were already apart of the domain. My problem is that i want to add them to the exchange server and i can not get them connected because when i hit check name it never checks out correctly. am i missing something. This is a fresh first install of exchange and i can get the administrator account to get email. I just can't get it to work on any users. In news:OGRukk%23QGHA.4608@tk2msftngp13.phx.gbl, Andrew Mallette <Andrewm270@aol.com> typed: > i've just added exchange 2003 to a small bu...

Method of calling a function where function name is stored in a variable or control
Is there a way to call a function (or sub for that matter) where the function/sub name is stored in a variable? For instance lets say I have a table that stores some function names. On a form I have a list box that displays the records in that table. For this example lets say they all accomplish similar things and all require the same numbef of arguments and none of them return a value. If Call were the right method to accomplis this it might look sometihng like this Dim lngArgument as Long lngArgument = 1234 Call Me.lstBox, lngArgument In this case Call would run the function...

Counting highlighted cells?
Is there a way to count cells in a column which have been highlighted or text that has been italicised??. As i have a column full of dates, and when something is completed the suggested date is changed to the actual date, highlighted and italicised. I need to keep a count on the completed dates. Check out http://cpearson.com/excel/colors.htm In article <BC6F544F-DF88-41A8-8F15-5A8D460F73FE@microsoft.com>, "Josh - Westfield Australia" <Josh - Westfield Australia@discussions.microsoft.com> wrote: > Is there a way to count cells in a column which have been ...

Formatting Col C based on data in Col B
I have to format a report every day that is imported from SQL to Excel. My problem is that I am stuck on trying to "insert" text descriptions in Column C based on what is in Column B. The number of rows may vary from day to day (ie: one day the report is 315 rows and the next it may be 278 or 480). So, the total range of Col B would extend from (B2:end) on any given day. In plain language, If any of the data in Range (B:B) begins with "ML*" insert UPPERCASE "ABC" in Col C2 or If any of the data in Range(B:B) begins with "W*" insert UPPERCASE ...

Two formulas in on cell based on two numbers in another cell?
Hi, Not sure this is possible but...I have a cell that has a number range in it and based on an amount in another cell want to calculate a new range. For example: Initial Range: A1 = 10 - 12 Calc Amount: A2 = 5 Final Range: A3 = 50 - 60 I think I can get the results by concatenating two formulas I'm just not sure how to enter the original numbers (A1) or how to distinguish between the two in the final formula (A3) Using Excel 2003. Hope this makes sense. Thanks. I would put the range in two different cells (eg A1 and B1). Then the multiplication is easy. If you ...

Change Legend Entry label of "Total" to actual name of field.
I have two pivot charts. One has multiple criteria and when I create a pivot chart, each series field breaks out properly using the names provided in the multiple criteria given. Now, the issue is when I have a single (one) field name, the chart automatically has the legend entry of "Total" since I am using the 'sum of' option. I've manually changed the name of the field in the pivot table, but the label on the chart remains the same. I've go so far as to read several tips on going to the source data to change this but of course when working with pivot tables, you ca...

Split Form with need to Provide Selection List
We have an Access 2007 application that has a split form (built on Table-1). We now need to add some type of facility that will allow our users to view data from Table-2 (while working on the Split form based on Table-1). They will then select one row from Table-2 which will contain 6 fields which we need to plug into Table-1. We are still fairly new to Access and we are not sure of the best way to accomplish this task. Thank You, Brad ...