Hi. I've got a cell with codes, such as:
21ML,
43GKP,
etc.
I want these to look like :
ML21,
GKP43,
i.e. swap the numbers with the letters but keeping the order of the characters.
I can extract the number with :
This code is from Microsoft and works fine. But now I need something to get the text from the code, to then concatenate it with the number.
Please Login or Register to view this content.
Any ideas?
If you're open to adding a new function to your workbook, this can do it simply for you.
==========Please Login or Register to view this content.
How to install the User Defined Function:
1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet
The function is installed and ready to use.
============
The new function is used like so...if the text string is in cell A1, this formula will convert the string the TxtNum format:
=SWAP(A1)
The new function has an optional second parameter, so if you ever want to switch the result and get NumTxt, use this instead:
=SWAP(A1, "n")
_________________
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!)
A VBA approach:
EDIT: went away and JB has now catered for your needs.Please Login or Register to view this content.
In formula terms, assuming strings in A1, A2 etc...
of course if the numbers are always 2 digits in length......Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
How about this:
EDITED to include this shorter alternative:Please Login or Register to view this content.
Last EDIT (hopefully)Please Login or Register to view this content.
If the cell may contain leading zeros:
Is that something you can work with?Please Login or Register to view this content.
Last edited by Ron Coderre; 10-23-2009 at 01:31 PM.
Very slick Mr C - that is why you are an MVP !Originally Posted by Ron Coderre
EDITED to include this shorter alternative:
Please Login or Register to view this content.
(I wish I could think like that!)
Thank you, my friend.
Sometimes...for the briefest of moments...I feel like I know what I'm doing.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks