• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to split text numbers from a cell into different cells using formula

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,


I have following values.


cell a1 - abc123


cell a2 - a1b2c3


cell a3 - 1a2b3c


I want result in following way using formula.


cell a1 - abc123 cell b1 - abc cell c1 - 123


cell a2 - a1b2c3 cell b2 - abc cell c2 - 123


celll a3 - 1a2b3c cell b3 - abc cell c3 - 123


I tried using Len, Right but not able to do it.

Can someone please help me in this.
 
Have a look at:


Remove all text:

http://www.mrexcel.com/forum/excel-questions/545660-macro-delete-all-text-cells-leave-numbers.html


Remove all Numbers:

http://stackoverflow.com/questions/3580203/how-to-remove-all-numbers-from-a-cell-with-a-function-or-regex
 
Hi Sachinbizboy,


Splitting is not so difficult, concatenation with formula is very difficult, so you can split things, but when you will concatenate, it will be difficult. Search out Sajan's post and you will find one example of concatenation by formula.


Regards,
 
Faseeh, concatenation is easy, no? =A1 & B1 is concatenation.

I'd say it was the other way around, splitting things is very hard, putting things together is very easy. Or am I missing your intent?
 
I was searching for an example. I tried this concatenation several times and usually it is more difficult and vba-based then splitting. But that is an opinion, you can ignore it happily :)
 
I'd say that dynamic concatenation or concatenation across large ranges can be pretty tedious, given Excel's concatentate formula doesn't accept ranges. Maybe that's what you had in mind?


Sorry, wasn't trying to challenge your opinion so much as understand it :-)
 
Hi Sachin ,


As Faseeh has already mentioned , it is easier to extract numeric values from an alphanumeric string , since numbers can be concatenated together by multiplying by powers of 10 ( which is the same as multiplying by 10 again and again ) and adding.


For the formula , check this link :


http://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html


Narayan
 
Faseeh - I think I understand now...concatenation of an array that is derived as the result of a formula seems to be impossible to do with a formula based approach in a single cell. I thought you were talking about concatenation of cells. My bad.
 
Faseeh & Narayan Sir thanks a lot for the help.


I will use B1: =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))*

ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)


http://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html


Have a nice day.
 
Hui Sir, thanks a lot for the help.


I am using following code to remove the numbers from the cell.


http://stackoverflow.com/questions/3580203/how-to-remove-all-numbers-from-a-cell-with-a-function-or-regex


Sub clear()

s = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9")

For Each r In Selection

v = r.Value

For i = 0 To 9

v = Replace(v, s(i), "")

Next

r.Value = v

Next

End Sub
 
You must log in or register to reply here.
Back
Top