here i given example file, please help me to split numbers and data,
my data like this..... 454875 - CALLER TUNE ACTIVATED, I WANT SPLIT NUMBERS IN ONE CELL AND OTHER SENTENCE SHOULD BE IN ANOTHER ONE CELL, CONSUME THIS MINUS - POINT IS CENTER.. BETWEEN TWO CELLS...
In cell B5: =LEFT(A5,FIND("-",A5,1)-2)
In cell C5: =RIGHT(A5,LEN(A5)-FIND("-",A5,1))
Copy to down/up to applicable cells.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
for the numbers...
=TRIM(LEFT(A2,SEARCH("-",A2,1)-2))
for the text...
=MID(A2,SEARCH("-",A2,1)+2,99)
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
hi i need to split this type of numbers also, in your formula i changed "-" this into "|" its not working,
i given example file..
your previous formula working well thank you alansidman and fbibbins... than you verrrry much.
Last edited by vengatvj; 10-15-2013 at 03:47 PM.
Use the built in Text-to-columns function in Excel and use | as your separator
is there options automatically split ? using permanent formula, its collapsed my data and its too sensitive also, just i paste entire number in one cell then automatically split as i given example file, will be better if happen as i told..
Last edited by vengatvj; 10-15-2013 at 04:14 PM.
pls find this excel
I don't understand what your issue is. Why cannot you use the text to columns function. It appears that you are trying to over-complicate a simple issue. Are you familiar with the text-to-columns functionality?
Review this tutorial http://www.youtube.com/watch?v=neDUOYk8hyU
Its ok because i had lot of datas in a excel, difficult to take along that mentioned data from my excel, so that only i asked, now ok i found another one way, thank you all
I have to agree with Alan on this. I see at least 5 sets of numbers in there, and while this is entirely possible to do wit formulas, it will be far easier and quicker to do with Text2Columns
I tried this in B Column, i had some formula in G, H, I, K,... columns ... if i do text2columns then it remove G,H,I,K datas.. extract with adding new columns.. so that only problem, now i copy in new sheet what i need to extract, and recopy extracted columns into my original excel.. now its ok, problem solved.. thanks for suggestion all
Happy to help
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks