Splitting Text

[tc88]

Text functions were never my specialty, but I am looking to create a lookup table for another worksheet based on the below information. I would like the numbers on the left side of the cell before the " -- " in the middle in one cell (say A2), and then the name on the other side of the " -- " in the next column (say B2).

Excel Workbook
	D
13	12487 -- Dummy Data
14	12488 -- These can be variable in length
15	1004001 -- But I need the full text
16	12720 -- Dummy data 2
Group Lookup Table

Excel 2010

 

[VoG]

Try like this

Excel Workbook
	A	B	C
1	12487 -- Dummy Data	12487	Dummy Data
2	12488 -- These can be variable in length	12488	These can be variable in length
3	1004001 -- But I need the full text	1004001	But I need the full text
4	12720 -- Dummy data 2	12720	Dummy data 2
Sheet4

 

[tc88]

Try like this

Excel Workbook
	A	B	C
1	12487 -- Dummy Data	12487	Dummy Data
2	12488 -- These can be variable in length	12488	These can be variable in length
3	1004001 -- But I need the full text	1004001	But I need the full text
4	12720 -- Dummy data 2	12720	Dummy data 2
Sheet4

Bingo! Thanks a bunch, I knew I was missing something (forgot the LEN argument). This forum is great.

 

[VoG]

You are welcome. You could also use Data > Text to Columns, Delimited, Specify Space and - as delimiters and tick Treat consecutive delimiters as one, then click Finish.

 

[R Pelle]

Excel provides a button on the toolbar called Text To Columns. It resides on the DATA ribbon (data tools section)
Try that one out too.

Keep in mind you may want to convert the results of Peter's solution to values if you are deleting your Column A.

If you use the TtoC button you may need to rid yourself of leading spaces.

Rob

Edit: treed :D