I am looking for a script to cut a portion of a cell's contents and then paste that cut portion into the cell in the next column.
Sample data:
Column A
30 tablets
100 soft gels
50 capsules
1 unit
3 bottles
4 ounces
Desired output:
Column A
30
100
50
1
3
4
Column B
tablets
soft gels
capsules
unit
bottles
ounces
The first characters will always be numbers, then a space followed by text. There will always be a space between the number and the text. Any help is greatly appreciated!
Evan
put this in cell b1
and this in cell C1
Please Login or Register to view this content.
it will do the samePlease Login or Register to view this content.
--
Regards
PD
----- Don't Forget -----
1. Use code tags. Place "[code]" before the first line of code and "[/code"]" after the last line of code. Exclude quotation marks
2. Thank those who have helped you by Clicking the scales above each post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Code wise - one possible approach:
assumes strings are in A [and of reasonable length] - adjust as nec.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
or
Please Login or Register to view this content.
@snb, IMO use of TtC is risky given we don't know:
If we assume that TtC is viable then IMO it would still be a good idea to add code to subsequently "reset" TtC from Space delimiter - so as to prevent unexpected separation going forward.a) number of spaces that may occur in any given string
b) whether or not we can override C etc...
@DonkeyOte
My suggestion is based on the information the OP provided.
Since no example workbook is posted I do not 'create' more constraints than presented by the OP.
If TexttoColumns is always specified for all kinds of delimiters 'resetting' shouldn't be necessary.
If you copy & paste text from an external app. into Excel then you are open to the last TtC delimiter being applied to the copied value(s).Originally Posted by snb
IMO it is a good idea to reset TtC delimiters though as mentioned that is my subjective opinion and I do not expect you to agree...
Nevertheless I like your pleonasms ....
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks