Unconcatenate

[Guest]

My question is : How do I UNconcatenate the data below into its original form
of cell A1 & B1

-Data Form-
Concatenate result: XZU422R-HKMRS3-2312
Cell: A1: XZU422R-HKMRS3
Cell: B1: 2312
=concatenate(A1,"-",B1)

But how do I UNconcatenate it to its original form of cell A1 & B1? ie 1
cell become 2 cells and it is formula base.

Thank you.

 

[Guest]

One way which might suffice, assuming data is representative ..

Assuming C1 houses the concat formula:

=concatenate(A1,"-",B1)

In D1:
=LEFT(C1,SEARCH("-",C1,SEARCH("-",C1)+1)-1)
D1 returns: XZU422R-HKMRS3

In E1:
=RIGHT(C1,LEN(C1)-SEARCH("-",C1,SEARCH("-",C1)+1))
E1 returns the text number: 2312

And if you want the text number in E1 returned as a real number
just "+0" to it, ie put instead in E1:
=RIGHT(C1,LEN(C1)-SEARCH("-",C1,SEARCH("-",C1)+1))+0

---

 

[Guest]

gosh,,,,kinda question of which comes first "the egg or the chicken"...
A1 and B1 is already infront of you on its original form..
you can just transfer it to another cells as follows...
D1:=A1
E1:=B1
u may have trouble if the A1 and B1 comes from a separate or deleted
workbook. wherein search/update linking is not anymore valid. good luck

 

[Guest]

zzzzzzzzzzzzzzzz?

 

[Max]

:
....

D1:=A1
E1:=B1

Ahh, but of course. But do you think that's what is actually asked here? See
the OP's subject line. Sometimes, one has to read subtly beyond the obvious
that's posted.

This particular line in the OP was also key:

But how do I UNconcatenate it to its original form of cell A1 & B1? ie 1
cell become 2 cells and it is formula base.

---

 

[Max]

zzzzzzzzzzzzzzzz?

I wonder how this kind of response helps ..

---

 

[Guest]

sorry max, its not suppose to oppose your logic. The ? is will this formula
work-out when the referred A1 and B1 is not in the same workbook. lets say
that the formula exist yet the source workbook is corrupted, to have a sense

 

[Max]

Think the OP is simply asking how to split a text string using formulas
here.

 

[Guest]

Yah me also, but the OP's clear about A1 & B1...anyway let him sweat..thanks

 

[Max]

.. anyway let him sweat ..

You must have made a typo somewhere above ..
Don't think that should ever be the objective of any response to OPs

---

 

[Guest]

....just to realize the unspoken...hope he's still out there...let's proceed

 

[Guest]

Thanks Max. Appreciate it.

One way which might suffice, assuming data is representative ..

Assuming C1 houses the concat formula:

In D1:
=LEFT(C1,SEARCH("-",C1,SEARCH("-",C1)+1)-1)
D1 returns: XZU422R-HKMRS3

In E1:
=RIGHT(C1,LEN(C1)-SEARCH("-",C1,SEARCH("-",C1)+1))
E1 returns the text number: 2312

And if you want the text number in E1 returned as a real number
just "+0" to it, ie put instead in E1:
=RIGHT(C1,LEN(C1)-SEARCH("-",C1,SEARCH("-",C1)+1))+0

 

[Max]

You're welcome !