text separation by formula, please have a look color marks text
cell A1 = +819038031597 · k123hiro@gmail.com · Japanese
from above data i need to separate Mobile number & email number like below
cell B1 = 819038031597
cell C1 = k123hiro@gmail.com
Again for below
Cell A2 = Thursday, May 2, 2013 at 18:35:21 · #664055610
from above data i need to separate Mobile number & email number like below
cell D1 = Thursday, May 2, 2013
cell E1 = 664055610
In B1: =MID(A1;2;12) (assuming that all phone numbers are 12 characters long)
In C1: =MID(A1;FIND("*·*";A1)+3;FIND("*· ";A1;FIND("*·*";A1))-FIND("*·*";A1)-3)
Maybe my semicolons should be commas for you
When I say semicolon, u say comma!
In D1: =LEFT(A2;FIND("*at";A2)-1)
In E1: =RIGHT(A2;9) (assuming all email numbers are 9 characters long)
Let me know if my assumptions are wrong, then I can come up with another formula for you
Wow don't know what happened, the *'s in my formulas were spaces. But I had some trouble with your data (had to copy some characters from them because they weren't what they appeared to be), so maybe try to recreate my formulas instead of copying them
@L-Drr
If you want the file, see this link.
http://www.excelforum.com/excel-prog...56#post3415056
@nur2544
In future stick with the original question, according the forumrules (see below).
This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Dear oeldere , thanks for your concern degrading this , with respect you , now come to the point I was raised a thread & after some time I got a response from you which was really appreciated. but that macros output was not fulfillment my requirements , which I mentioned you like below comments.
And I was waiting for your reply , when I saw that I didn’t get any reply from you, I assume that my thread may be complex. after that I thought alternative way and that is formula solution (though it will not be fulfillment my requirement , but it will partial help me)Hi oeldere, thanks for replay, It is not working for me , please check the attachment, here I have mention that Column A is my input data. And from this input I need output as like row J2 to O2 .
I think it was my fault , I might not ask the question properly, but I was replay also with my actual asking ,how ever I always respect all forum expert those are working for us with open heart . I always try to abide by all the forum Rules & regulation of excelforum. Some time there might be mistake due to lack of my English competence.
Hi L-Drr , thanks for replay, below formula showing #VALUE!, rest are working fine.
Please Login or Register to view this content.the characters of Mobile number & email number might be varies that is also problem. however please don't wast your valuable time regarding this.(assuming that all phone numbers are 12 characters long)
(assuming all email numbers are 9 characters long)
i will do my job manually what i am currently doing. thanks for your supports..
Try replacing the characters in the FIND() phrases with a literal copy of some of your actual data. So instead of typing "space-dot-space", copy those characters from a cell in which the formula in looking
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks