Unconcatenate
 

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.

Unconcatenate
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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.

Unconcatenate
 

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

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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.

Unconcatenate
 

zzzzzzzzzzzzzzzz?

" wrote:

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.

Unconcatenate
 

"driller" wrote:
....
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Unconcatenate
 

"?" wrote:
zzzzzzzzzzzzzzzz?

I wonder how this kind of response helps ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Unconcatenate
 

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
on the thread reading by other viewers. Dont you think so?

"Max" wrote:

"driller" wrote:
....
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
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Unconcatenate
 

Think the OP is simply asking how to split a text string using formulas
here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"driller" wrote in message
...
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
on the thread reading by other viewers. Dont you think so?

Unconcatenate
 

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

"Max" wrote:

Think the OP is simply asking how to split a text string using formulas
here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"driller" wrote in message
...
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
on the thread reading by other viewers. Dont you think so?

Unconcatenate
 

.. 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Unconcatenate
 

....just to realize the unspoken...hope he's still out there...let's proceed
on another sensible thread...to be good for both us...999

"Max" wrote:

.. 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Unconcatenate
 

Thanks Max. Appreciate it.

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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.

Unconcatenate
 

You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote in
message ...
Thanks Max. Appreciate it.