Reverse Text in Cell before and after comma (not just Word1, Word2

I've seen some workarounds for doing

Smith, John

to 

John Smith

But I am looking for

Word1 MaybeWord2-3, WordA MaybeWordB-C

i.e. just put all the words after the comma in front and vice versa e.g.

Assistant Manager, Building Services=Building Services Assistant Manager
Manager, Building Services = Building Services Manager
Assistant Manager, Building = Building Assistant Manager

Is there an 'easy' way to do this with cell functions vs VBA?

Thanks in advance!
0
Utf
12/24/2009 11:45:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
752 Views

Similar Articles

[PageSpeed] 19

I sometimes break this into smaller pieces.

Say your data is in column A (A1:A3)

I'd put this formula in B1:
=SEARCH(",",A1)
This returns the location of the first comma in A1.

Then since I want everything before that first comma, I'd use a formula like
this in C1:
=TRIM(LEFT(A1,B1-1))
I added trim just in case there were leading/trailing spaces.

Then for the stuff after the initial comma, I'd use a formula like this in D1:
=TRIM(MID(A1,B1+1,255))
255 is just a number big enough for the longest string that you have.  Make it
bigger than what you need and it'll be ok.

Then I just have to concatenate the strings in C1 and D1 (well, D1 and C1) with
a formula like this in E1:
=D1&", "&C1

Then I drag all the formulas down as far as I need.

I'll convert the formulas in E1 to values (edit|copy, edit|paste
special|values).

And delete the other columns (B:D and maybe A???)

You can do it in a single formula if you want:
=TRIM(MID(A1,SEARCH(",",A1)+1,255))&", "&TRIM(LEFT(A1,SEARCH(",",A1)-1))

Personally, it doesn't bother me if I have to use intermediate cells--especially
if I'm gonna delete them before I release the workbook to others.

msnyc07 wrote:
> 
> I've seen some workarounds for doing
> 
> Smith, John
> 
> to
> 
> John Smith
> 
> But I am looking for
> 
> Word1 MaybeWord2-3, WordA MaybeWordB-C
> 
> i.e. just put all the words after the comma in front and vice versa e.g.
> 
> Assistant Manager, Building Services=Building Services Assistant Manager
> Manager, Building Services = Building Services Manager
> Assistant Manager, Building = Building Assistant Manager
> 
> Is there an 'easy' way to do this with cell functions vs VBA?
> 
> Thanks in advance!

-- 

Dave Peterson
0
Dave
12/25/2009 1:03:50 AM
On Thu, 24 Dec 2009 15:45:01 -0800, msnyc07 <msnyc07@discussions.microsoft.com>
wrote:

>I've seen some workarounds for doing
>
>Smith, John
>
>to 
>
>John Smith
>
>But I am looking for
>
>Word1 MaybeWord2-3, WordA MaybeWordB-C
>
>i.e. just put all the words after the comma in front and vice versa e.g.
>
>Assistant Manager, Building Services=Building Services Assistant Manager
>Manager, Building Services = Building Services Manager
>Assistant Manager, Building = Building Assistant Manager
>
>Is there an 'easy' way to do this with cell functions vs VBA?
>
>Thanks in advance!


=TRIM(MID(A1,FIND(",",A1)+1,99) & " " & LEFT(A1,FIND(",",A1)-1))

--ron
0
Ron
12/25/2009 1:14:06 AM
Assuming no leading or trailing spaces and always a comma/space separator...

=MID(A1&" "&A1,FIND(",",A1)+2,LEN(A1)-1)

-- 
Rick (MVP - Excel)


"msnyc07" <msnyc07@discussions.microsoft.com> wrote in message 
news:E421F372-76D5-4EB1-9F61-7A4F5DCE1CAA@microsoft.com...
> I've seen some workarounds for doing
>
> Smith, John
>
> to
>
> John Smith
>
> But I am looking for
>
> Word1 MaybeWord2-3, WordA MaybeWordB-C
>
> i.e. just put all the words after the comma in front and vice versa e.g.
>
> Assistant Manager, Building Services=Building Services Assistant Manager
> Manager, Building Services = Building Services Manager
> Assistant Manager, Building = Building Assistant Manager
>
> Is there an 'easy' way to do this with cell functions vs VBA?
>
> Thanks in advance! 

0
Rick
12/25/2009 1:46:51 AM
Reply:

Similar Artilces:

Summing instances of text across worksheets
Hello, I have a workbook with separate sheets for each month. The sheets contain a list client names (entered exactly the same on each sheet). I need to summarize how many times a client name appears in total. For example, John Brown may appear Jan & Feb (=2) where Mary Brown may appear Jul, Sep, Dec (=3). I've been trying pivot tables without luck. Hoping someone can help. Thanks in advance! Never mind, I consolidated all the worksheet data into one and the pivot table works fine. Thanks anwyay! "George" wrote: > Hello, I have a workbook with separate sheets fo...

Outlook 2000 just received mail...
....but checking is *off*. I have a spam filter which I manually fire up to process all mail, therefore I turn checking off in Outlook 2000. So why did I just receive some? Very strange... Neil --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.509 / Virus Database: 306 - Release Date: 12/08/2003 ...

Find feature does not work with HTML messages (Content-type: text/html)
I have Outlook 2000 SP-3 running under Windows ME. When using the Find feature to locate all e-mails with a given word in the message, no items are found if the e-mails are of "text/html" content-type. The Find feature appears to work for any messages that have URLs embedded and are "multipart/alternative" or "text/plain" content-type. I noticed a similar post several months ago (see http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=%23qSBYIssDHA.2224%40TK2MSFTNGP09.phx.gbl). Is there a work-around for this problem, or should this be reported t...

too many different formatting cells
I can't open an excel document because whem I try to open it says that too many different formatting cells. How to resolve this and open this document? Maybe... XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=213904 A few people have said that OpenOffice.Org has been able to open the file. Then they clean it up and save it there. Then excel can open that cleaned up version. http://www.openoffice.org, a 60-104 meg download or a CD jo wrote: > > I can't open an excel document because whem I try to open it says that too >...

Excel should default to autoformat for chart text boxes
Excel in (Office 2003 and as far as I can tell all previous versions of Excel) should default to autoformat for chart text boxes - it currently selects "none" for outline and background options ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.micr...

Use cell value as cell address
Hello everyone. I have a worksheet "Main" of 39,000 rows in which column B contains a number between 1 and 7,500. Column C is an empty column I have added. The second sheet, "Names" in the book contains a single column - A - of 7,500 names. I want to get the value from the second sheet that matches the number column of the first sheet. In other words, if "Main" cell B3 contains 3780, I want to put the value from "Names" cell A3780 into "Main" C3. How do I do this please? Richard --- Message posted from http://www.ExcelForum.com/ Hi tr...

Just ordered Quicken 2010 to replace Money Plus Premium
Going to give it a shot and see what happens. I've read threads here and other places and it seems that the problems are usually minor and correctable. Don't recall any showstoppers. Has anyone accumulated the issues and workarounds where necessary, or suggested tips, like removing the password first? I have data going back to 1995 (I've had every version of Money since then) and last time I looked, it was over 60,000 transactions in total. Also, unrelated, but is there an Excel addin like MoneyLink that lets you import data directly into Excel? I have found that feature insanel...

Formulas showing in cell???
I keep getting a formula showing in the cell after I edit i (occasionally). Show formulas is turned off and edit in cell is on. How can I make the formula go awaw and simply show the results whic when edited the results shows correctly? -- Message posted from http://www.ExcelForum.com Hi probably the cell is formated as 'Text' change the cell format to 'General' and re-enter your formula -- Regards Frank Kabel Frankfurt, Germany > I keep getting a formula showing in the cell after I edit it > (occasionally). Show formulas is turned off and edit in cell is on. >...

Need help extracting text from EDLs
I really am trying to learn this stuff, but I haven't programmed anything since my TRS-80 Basic days and I'm a bit overwhelmed. I want to extract text from Edit Decision Lists so that I have a list of shots used in a film. Here is an example of one event of an EDL: 002 TAPE004 V C 04:45:22:06 04:45:24:14 02:00:08:00 02:00:10:08 FROM CLIP NAME: 7C-4_B_ DLEDL: PATH: /raids/luc_1/bun/reel_02/oscans/1222/bun_7c_4_b_02/2048x1556/ The text that follows "FROM CLIP NAME:" is the name of the clip that I want to extract so that I have a document on onl...

Automatically display set text based on users composition
Hi, im trying to do something really simple, trouble is i dont know what the feature's called to be able to search for tips on how to do it. Basically in outlook messages, when a user begins writing a sentence e.g. "in the terms of" i need a tag to pop up that allows the user to press enter and then the remainder of what they will want to type in will be inserted in, its a yellow tag that comes up above the words. i dont know where it needs to be created and enabled. Cheers, Rhys. ...

Text-To-Columns Fixed Width
When using Text-To-Columns, Excel "recognizes" that the data fits the Fixed Width criteria and PRE-assigns the width. In most of the cases I work with Excel is wrong 99% of the time. Is there a way to force Excel to NOT pre-assign the width (leave everything blank)? On the first panel of the wizard click Delimited, then on the second panel click space as the delimiter. Hope this helps. Pete On Feb 4, 12:50=A0am, TP <T...@discussions.microsoft.com> wrote: > When using Text-To-Columns, Excel "recognizes" that the data fits the Fix= ed > Width ...

How to get total "conditional sum of cells" in a column?
Hi all, I have dollar amounts in one col, and status in another. I want the sum of those dollar amounts where the corresponding status cell is empty (blank). How do I do this? Thanks for any hints, cdj Status in Column A and dollar amounts in Column B: =SUMPRODUCT((A2:A100="")*B2:B100) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.go...

How to only "paste values" of cells that are not "hidden"?
Hello, In my document, many columns are hidden. Say column B is hidden, and I need to copy columns A and C and paste values into another Excel document. How can I do that? When I select columns A and C, copy and paste values, the other document contains columns A,B and C, instead of just columns A and C. Thank you! Don't drag-select, control-click A and then C, for scattered-selection. Danny On Sun, 17 Jul 2005 16:33:01 -0700, Sam <Sam@discussions.microsoft.com> wrote: > Hello, > > In my document, many columns are hidden. Say column B is hidden, &g...

Code to send SMS text message?
Hi, I would like to send an SMS text message to a cel phone via code within Excel 2007. Has anyone done this? What code / services / products do I need to accomplish this? Does anyone have an example? Thanks in advance. --Dan What gateway are you using? What you would need to do is to snd a internet message to a gateway provider which would then send the messae out on the wireless land. Most likely you are using a webpage interface from your PC to interface with the gateway. So opening up an internet explorer and goiong to an URL is probably the method to use. ...

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

Access 2007 Rich Text Question 04-28-07
Hi All In the plan text version of a Memo field you can easily add a date by pressing Ctrl+Colon which makes life easy for the end user. However, if the Memo field is set to the Rich Text Property this no longer works. Or am I missing something? Tom gave me a part answer last week but I cannot see a way of inserting a date on a Rich Text Memo field by using keystrokes Thanks ...

how do i copy a formula when cell references r not together
in cell reference H5 i have a formula H4*H3, I have copied this formula through to DG5. In cell reference H7 I have a formula H6*H3 which i have also copied through to DG7.I have formulas right down to cell reference H299 (H298*H3) Is there a quick way to repeat the copy bearing in mind the cell references are not together ie H5, H7, H9 and so on. Jon, Copy cells H6:H7, then select H6:H299 and pastespecial formulas. Just make sure your formula is =H6*$H$3 HTH, Bernie MS Excel MVP "jon104" <jon104@discussions.microsoft.com> wrote in message news:DDAB488A-5CDA-47A2-AD...

Text box and formatting?
I am working on class schedule and have one text box which spans a few pages. Pages are double columns. I have a header for each class and paragraph given for class description. On the last page, last column, one description appears at the top with empty space for the rest of the column. I would like to change the formatting of the text in the whole text-box so that most of the last column is used. I can select the text in the text box which expands over the few pages and play with the font size, but that would mean that all the text would be the same size. Is there a way that text...

Scrolling through cells
I'm having trouble scrolling through the cells using the arrows. When I was typing in a cell I used to be able to press one of the arrow keys and it would automatically go to the next cell. Now when I press the arrow key is sticks the next cell in as part of a formula. I'm not sure what's happened to change this. Can anyone help???? thanks :) You are probably in Extend mode. This happens when you press F8. It shows "EXT" in the Status bar (to the right) Press F8 again to deactivate this. -- Kind regards, Niek Otten Microsoft MVP - Excel "alice" &l...

Overwriting a cell with a formula without deleting the formula
Hello. I am creating an Expense Report worksheet and have created a simple formula that will calculate mileage based on total miles. Below is my worksheet data. A B C D 1 Expense Type Acct. Code Total Miles Amount 2 Airfare 11111 $250.00 3 Mileage 22222 20 $10.00 I am trying to figure out a way to create a conditional formula so that IF Expense...

Highlight changes within cell
Good morning! Using Excel 2003 I need to highlight the changes I’m making within a cell. For example: In cell B2, is the customer’s original order quantity of 200. I revise it to show 225 and I’d like the cell to be highlighted in yellow. I can then copy and paste the info into an email to show the customer which items have been revised. I’ve tried using Track Changes, but it seems that I have to click on the Track Changes button each time I open the workbook. It also doesn’t keep the revision highlighted for a copy and paste. I have 20+ worksheets within the workbook an...

How do I use text in a cell as a range name in a formula
If cell A1 had the text TEST in it and TEST is the name I have given to a group of cells using the name box what formula can I use to give me the sum of TEST, thats is the sum of the cells in the group called TEST. I understand that I can simply have =SUM(TEST), but I want the formula to refer to Cell A1 to get the name ie =SUM(A1) doesn't work obviously Any help appreciated Thank you In this case, you want to use the INDIRECT function. E.g., =SUM(INDIRECT(A1)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kiwi" &...

deleting duplicate cells
I am back again... Thanks to everyone's help here last time, I was able to finish all my work and do it correctly. Ken- I asked last time i was here about deleting duplicate cells. Some of the names(address, etc) are repeated in my sheet. I want to delete the extra cell of the people who are in here twice. Not jus the cell but their record, name, address, city, state zip when they are in their twice so that they will only be listed once. You told me how to do this once but i cant find where it is on the board. Thanks for all the help.... BR4 -----------------------------------------...

Bug? Multiple values in merged cells
I found that merged cells can contain multiple values. Steps to reproduce: Type 1,2,3,4 in a1:d1 type sum(a1:d1) in e1 Select a1:b1 and merge Warning : MultipleData, overwrite? Say yes to merge Select the merged a1:b1 cells Copy Select c1 PasteSpecial Formats No warning.. no overwrite. c1:d1 are now merged BUT d1 still contains a value... and the SUM of a1:d1 = 8 !! Also happens with FormatPainter etc Behaviour observed in xl97,xlXP and xl2003 Error checking will find no fault in the sheet... and you can spend ages to find out WHY your cross sums dont match! (although now that i fou...

Find Blank Cells
I wish to replace the blank cells in a large database with a zero (0). I cannot figure out how to find a blank cell using the Find and Replace option under the Edit menu. Can anyone show me the way? Hi Peter, I always use CTRL+H to bring up the find and replace menu, leave the fine part empty, and put in what you want to replace it with....however, I do notice you say in a "database"? Do you mean in Access?? Dave M. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Exc...