split txt in column

[max_max]

Hello to all,
how can I split these texts in 2 columns?:

Acciaio al carbonio 7,85
Acciaio comune 7,85
Acciaio inox 304 7,93
Alpacca 8,65
Acciaio laminato 7,85
Acqua 1
Acqua ossigenata 1,46
Amianto 2,5
Antimonio 6,7
Arenaria 2,3
Bronzo di alluminio 10% 8,7
Bronzo di alluminio 3% 7,7
Diamante 3,55
Eternit e simili 2
Ferro 7,85
Fosforo 1,98
Grasso lubrificante 0,93
Leghe leggere a base Al 2,68
Leghe leggere a base Mg 1,82
Legna giocchi 0,35
-----------------------------------------
colomn A | column B
Acciaio al carbonio | 7,85
Acciaio comune |7,85
Acciaio inox 304 | 7,93
Acqua | 1
Acqua ossigenata | 1,46
Amianto | 2,5
Antimonio | 6,7

I hope I explained,
Thanks in advance.
max-max

-----------------------------------------------------------------------------------------------------

Ciao a tutti,
Come posso dividere i testi in 2 colonne?:


spero di essermi spiegato,
Un grazie anticipato.
max-max

[sktneer]

Assuming your have data in A1, place the below formula in B2

Please Login or Register  to view this content.

Hope it helps.

[martindwilson]

b1=LEFT($A1,FIND("^",SUBSTITUTE($A1," ","^",LEN($A1)-LEN(SUBSTITUTE($A1," ",""))))-1)
c1=--MID($A1,FIND("^",SUBSTITUTE($A1," ","^",LEN($A1)-LEN(SUBSTITUTE($A1," ",""))))+1,255)

[max_max]

Hello sktneer,
your formula is not quite accurate.
The text should be in two columns.
example:

Acciaio al carbonio 7,85

column A = Acciaio al carbonio
column B = 7,85

------------------------------------------------------
Acqua 1

column A = Acqua
column B = 1

max_max

[XOR LX]

@sktneer

That solution will result in the numerical split for, for example:

Acciaio inox 304 7,93

as:

304 7,93

Since your desired split seems to always occur after the final space, try:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255))

Regards

[max_max]

Hello to all,
the exact formula is martindwilson
Translated into Italian:
=SINISTRA($A2;TROVA("^";SOSTITUISCI($A2;" ";"^";LUNGHEZZA($A2)-LUNGHEZZA(SOSTITUISCI($A2;" ";""))))-1)
=--STRINGA.ESTRAI($A2;TROVA("^";SOSTITUISCI($A2;" ";"^";LUNGHEZZA($A2)-LUNGHEZZA(SOSTITUISCI($A2;" ";""))))+1;255)
I also thank sktneer and XOR LX
max_max

------------------------------------------------------------------------------------------------------------------------

Ciao a tutti,
la formula esatta è di martindwilson
Tradotta in italiano:
=SINISTRA($A2;TROVA("^";SOSTITUISCI($A2;" ";"^";LUNGHEZZA($A2)-LUNGHEZZA(SOSTITUISCI($A2;" ";""))))-1)
=--STRINGA.ESTRAI($A2;TROVA("^";SOSTITUISCI($A2;" ";"^";LUNGHEZZA($A2)-LUNGHEZZA(SOSTITUISCI($A2;" ";""))))+1;255)
Ringrazio anche sktneer e XOR LX
max_max

[sktneer]

Sorry I mistaken that.

[XOR LX]

"Sorry I mistaken that. In that case
in cell B1
=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

in cell C1
=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))""

For Acciaio inox 304 7,93 those two give Acciaio inox in both B1 and C1.

In fact, your two formulae for B1 and C1 are identical (?).

Regards

[sktneer]

@ XOR LX
Thanks for pointing it out......

Actually the correct formula will be........
B1 = TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))
C1 = TRIM(REPLACE(A1,1,LEN(B1),""))

I hope this time it's ok.

[max_max]

Thanks XOR LX,
your first formula:
in cell B1
= TRIM (LEFT (A1, MIN (FIND ({0,1,2,3,4,5,6,7,8,9}, A1 & "0123456789")) -1))
and equal to the second?:
in cell C1
= TRIM (LEFT (A1, MIN (FIND ({0,1,2,3,4,5,6,7,8,9}, A1 & "0123456789")) -1))
max_max

[sktneer]

Sorry max_max. It happens sometimes with everyone.
Corrected the formula. See post #9.

[max_max]

Thanks sktneer,
Also your formulas are correct.
Translated into Italian:
=ANNULLA.SPAZI(SINISTRA(A2;MIN(TROVA({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))-1))
=ANNULLA.SPAZI(RIMPIAZZA(A2;1;LUNGHEZZA(B2);""))
max_max
------------------------------------------------------------------------------------
Grazie sktneer,
anche le tue formule sono corrette.
Tradotte in Italiano:
=ANNULLA.SPAZI(SINISTRA(A2;MIN(TROVA({0;1;2;3;4;5;6;7;8;9};A2&"0123456789"))-1))
=ANNULLA.SPAZI(RIMPIAZZA(A2;1;LUNGHEZZA(B2);""))
max_max

[sktneer]

Thanks for the feedback.

[XOR LX]

Still don't understand:

Using:

B1 = TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

C1 = TRIM(REPLACE(A1,1,LEN(B1),""))

on Acciaio inox 304 7,93 gives:

B1: Acciaio inox

C1: 304 7,93

I thought the desired result for this entry was:

B1: Acciaio inox 304

C1: 7,93

Regards

[max_max]

Using:

B1 = TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

C1 = TRIM(REPLACE(A1,1,LEN(B1),""))

on Acciaio inox 304 7,93 gives:

The result is:

B1: Acciaio inox 304

C1: 7,93

max_max

[XOR LX]

@max_max

I don't see how that is a possible result on that string using that formula.

Could you please re-check and/or post a workbook showing that result?

Many thanks

[max_max]

The workbook

pesi_specifici.xls

max_max

[AlKey]

In B1 enter an array formula (Ctrl+Shift and Enter)

=LEFT(A1,MAX((MID(A1,ROW($1:$100),1)=" ")*ROW($1:$100)))

In C1

=RIGHT(A1,LEN(A1)-SEARCH("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

	A	B	C
1	Materiali peso Kg/dm3	Materiali peso	Kg/dm3
2	Acciaio al carbonio 7,85	Acciaio al carbonio	7,85
3	Acciaio comune 7,85	Acciaio comune	7,85
4	Acciaio inox 304 7,93	Acciaio inox 304	7,93
5	Acciaio inox 316 7,98	Acciaio inox 316	7,98
6	Acciaio inox 321 8,02	Acciaio inox 321	8,02
7	Acciaio inox 400 7,73	Acciaio inox 400	7,73
8	Acciaio laminato 7,85	Acciaio laminato	7,85
9	Acqua 1	Acqua	1
10	Acqua ossigenata 1,46	Acqua ossigenata	1,46

[XOR LX]

@max_max

? Precisely as I said. The results in your workbook for Acciaio inox 304 7,93 are:

Column B: Acciaio inox

Column C: 304 7,93

and NOT

Column B: Acciaio inox 304

Column C: 7,93

as you stated (and originally requested).

Regards

[martindwilson]

does this not work? no unnecessary arrays !
and i agree with XOR LX