text to columns number like 978-123-0000

[lezawang]

Hi
Can I ask text to column to replace on the first - with column? in another words, can I ask text-2- columns to do this

978

123-0000

<tbody>
</tbody>

instead of this

978

123

0000

<tbody>
</tbody>

Thank you.

 

[sandy666]

you can try PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"})
in
    #"Split Column by Delimiter"[/SIZE]

Column1		Column1.1	Column1.2
978-123-0000		978	123-0000

 

[jtakw]

Hi,

A couple of formula options.

B1 formula copied across, OR
use B2 and C2 formulas:

Book1
	A	B	C
1	978-123-0000	978	123-0000
2	978-123-0000	978	123-0000
Sheet329

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE($A1,"-",REPT(" ",99),1),COLUMNS($B1:B1)*99-98,99))
B2	=LEFT(A2,FIND("-",A2)-1)
C2	=MID(A2,FIND("-",A2)+1,99)

 

[Peter_SSs]

.. or if the data structure is uniform (1 example isn't much to go on) you can do it with Text to Columns (Fixed width)