Solved

Reformat a field in Excel

Posted on 2012-03-31
4
203 Views
Last Modified: 2012-04-09
Hi, In Excel, how can I make  BROOKS, GARTH to GARTH BROOKS magically?
0
Comment
Question by:Computer Guy
  • 2
4 Comments
 
LVL 90

Expert Comment

by:John Hurst
Comment Utility
Assuming you can create a sheet with just this information in it (say a column created as you showed above), save the sheet as a .CSV file and the inport it. The CSV import allows you to define colums by the comma, which is what you want here. Then copy the columns back to your main sheet.

.... Thinkpads_User
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
if A2 has the lastname,firstname, you can put this in B2 and copy down:

[B2] = TRIM(RIGHT(A2,LEN(A2)-FIND(",",A2))) & " " &LEFT(A2,FIND(",",A2)-1)

This assumes that you may or may not have a space after the comma.

See attached.

Dave
example-r1.xls
0
 
LVL 3

Author Comment

by:Computer Guy
Comment Utility
Hi,

Thanks, I tried it and it works fine on the ones that are setup that way.

Now when I say have ALABAMA, this shows up in the field: #VALUE!

How can I do this automatically without having to change it manually?

Thanks!
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
Sorry - for some reason I didn't see your response until now.  I don't get that error in my test, however, I did add some error checking to the formula.  If you get an error in future, just put the text in a cell and upload this sample worksheet so I can diagnose, though hopefully we're now in better shape.

[B2] = IF(ISERROR(FIND(",",A2)),A2,TRIM(TRIM(RIGHT(A2,LEN(A2)-FIND(",",A2))) & " " &TRIM(LEFT(A2,FIND(",",A2)-1))))

And copy down.

See attached.

Dave
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now