This may be simple but what is a formula to switch the contents of cell A1 from "A,1" to "1,A". Essentially, the values before the comma and the values after need to be reversed. The formula needs to be flexible enough to account for strings like "A,12" where there are more than one number/letter on each side of the comma. Help would be greatly appreciated.
If the values are in A1 going down, put this formula in B1 and copy down:
=MID(A1, FIND(",", A1)+1, LEN(A1)) & "," & LEFT(A1, FIND(",",A1)-1)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
You can do this a few ways. The easiest will likely be the Data tab -> Text to Columns. Select the column and split the data by the comma. Then you can use a simple concatenate to bring them back together in the other order.
Another method would be to use the text functions as follows (assuming the data is in call A1, and your formula is in cell b1):
=RIGHT(A2,LEN(A2)-FIND(",",A2))&","&LEFT(A2,FIND(",",A2)-1)
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
The attached will separate by comma. Great info available here:
http://www.cpearson.com/Excel/Topic.aspx
http://www.cpearson.com/Excel/FirstLast.htm
Or
=TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",50)),50))&","&TRIM(LEFT(SUBSTITUTE(A1,",",REPT(" ",50)),50))
Row\Col A,1 1,A
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Awesome thank you all that works perfectly!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks