I have a list of sizes where the format is number and then text unit of measure--the number always comes first. If there is no space between the numbers and the letters I need to insert a single space. The number can be of varying length. The list is several hundred rows. Can a formula be written to do this? I've uploaded a small sample file that should pretty clearly illustrate what I am trying to do. Thanks for any help!
Last edited by dleslieca; 08-25-2017 at 05:29 PM. Reason: Added file with sample data
Formula:
Please Login or Register to view this content.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Last edited by mehmetcik; 08-25-2017 at 03:11 PM.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
The file I've attached has before and after columns--probably not necessary, but illustrates what I'm trying to do--using a helper column, of course.
Formula for B2
Formula:Please Login or Register to view this content.
Thank you for such a quick reply, but is there a way to do this without specifying the unit of measure? It can be OZ, CT, PR, etc., (I'm in a retail environment) so I'm hoping to not parse out each separately (i.e. =RIGHT(A2,2) and filter by UOM). Thank you!
Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):
=TRIM(REPLACE(A2,MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A2,ROW(INDIRECT("1:"&LEN(A2)))),0))+1,0," "))
Here's another way, but it's rather "dicey". If any of the UOMs can be interpreted by Excel as part of a number (date, scientific notation ... et.al.) this will not work on those.
That said:
In C2Formula:Please Login or Register to view this content.
Dave
That's perfect--thank you!
Dave, yours worked well also--thank you! I'll save both formulas for future reference--this probably won't be the last time I get poorly formatted data.
You are welcome. Thank you for the feedback and marking your thread Solved. It helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks