Hello,
Can you please help me with the following?
If column A1 through A4 has the following numbers/text in each cell:
abc123
abcd123
abcd1234
abcdefg12345
How do i add a space in between the text and numbers? For instance how do i make A1 look like "abc 123" instead of "abc123"
Thanks!
hi thanhie. if it's always text & then numbers, try this array formula:
=REPLACE(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),0," ")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Edit: don't need for CSE combination.
Last edited by benishiryo; 11-06-2013 at 01:43 AM. Reason: realized it doesn't need a CSE combination
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Is there another easy way to do this? For instance with the =find function =isnumber function or =replace function ? I remember coming across it before but i can't remember how to do it...sorry for being difficult but thank you so much for helping
Why don't you use the easy method suggested by Benishiryo?
Just curious to know the reason why you are looking looking for some other way?
From my point of view Benishiryo has given you the simple and efficient solution
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Ben's suggestion has given you 2 of the 3, and, like Six, Im unsure why that would not work for you? Is this a homework assignment?=find function =isnumber function or =replace function
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
does the number sequence always start with 1? or is it random?
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
try this one (array formula)
=REPLACE(A1,MIN(IFERROR(IF(--MID(A1,ROW($1:$100),1),ROW($1:$100)),"")),0," ")
it is random...could be 123 or 3456 or 8792383
It would be helpful if you could reply to post # 4 and # 5?
that's a puzzle to me also as Ford has mentioned what is the matter in the given solution which is one of the shortest/best method.
Ok try this one...
copy the data to MS WORD
click "Find and Replace" button located in the home tab
in Find type : ([A-z])([0-9])
in replace with type : \1 \2
note: the \1 \2
\1 spacehere\2
and be sure to click the more button "Use wild card" tick box
Then Hit replace all
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Answer to #4 and #5: No this is actually for work. I was hired into a position that my company had just created so everything i am doing is from scratch.
I've attached the excel report that pertains to this particular question. In column "O" i am trying to separate the letters and numbers apart with a space.
Thanks everyone!!
OK heres another option, it will strip out the ending text...
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre
Edit: I put this in R14, copied down
Last edited by FDibbins; 11-07-2013 at 09:53 PM.
to extract the unit, use this...
=MID(O14,LEN(R14)+IF(ISNUMBER(SEARCH(",",O14,1)),2,1),99)
would of been easier to just ask that to begin with
really you only have CS or EA to take out
so simply would work
Formula:
Please Login or Register to view this content.
Works perfectly! Thank you!Originally Posted by FDibbins
if you want the data to remain in Column O
seeing you only have two things to replace (ea or cs)
you may just be better off doing it the manual way
highlight column O then press CTRL H (Find and replace)
find "ea" and replace with " ea" (likewise for cs)
actually simplier still
Formula:Please Login or Register to view this content.
Just realized my initial question was not clear but I'm sort of glad it wasn't because now i learned two methods: 1) extract the numbers 2) add space in between the numbers and letters.
double post
Last edited by humdingaling; 11-07-2013 at 10:16 PM. Reason: double post
Thanks!!! =Dwould of been easier to just ask that to begin with
really you only have CS or EA to take out
so simply would work
Formula:Please Login or Register to view this content.
not a problem
probably best to start off with example to begin with next time
the very last formula i gave you is even better than the substitute in this case
it just adds a space before the last two characters regardless of UOM
Please Login or Register to view this content.
which means if you have PK/IN or some other UOM it will cater for that also
Happy to help and thanks for the feedback
by the way (if we were to assume units is always 2 characters), this would work too:
=REPLACE(O6,LEN(O6)-1,0," ")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks