How to split data based on hyphen [closed]

excel
calc

asked 2014-03-27 13:53:28 +0200

Ram.BM
36 ●8 ●11 ●14

updated 2014-03-27 15:19:34 +0200

Pedro

5599 ●16 ●44 ●89

I have data in on one cell like

columnA
VADODARA - 390010
KALWAKURTHY -509324
CHENNAI -600042

I need output like

columnA       columnB
VADODARA      390010
KALWAKURTHY   509324
CHENNAI       600042

Please tell me how to do this.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 15:53:52.170697

add a comment

1

answered 2014-03-27 15:05:34 +0200

w_whalley

3647 ●6 ●22 ●44

updated 2014-03-27 15:22:22 +0200

Pedro

5599 ●16 ●44 ●89

How about Data > Text to Columns, setting the Separator options to Separated by > Other - (hyphen)?

If trailing spaces on the text column cause problems, then do a search for spaces and replace with nothing.

edit flag offensive delete link

Comments

I didnot get your answer can you show me with one example

Ram.BM ( 2014-03-28 07:28:13 +0200 )edit

@Ram.BM: select the cells with the data you want to split into columns and then click on the menu Data and follow the steps described by w_whalley

Pedro ( 2014-03-28 17:32:04 +0200 )edit

add a comment

0

answered 2014-03-27 14:48:30 +0200

ROSt52

14225 ●109 ●162 ●188

The needed formula is to be seen in the screenshot.

image description

However, the formula requires that after the city name (any length of the citiy name is possible), there is blank followed by a hyphen, followed by a blank, followed by a 6 digit numercial or alphanumercial string. See also the red marks in the screen shot.

edit flag offensive delete link

Comments

@ROSt52, that does not separate the data to two columns as requested. w_whalley's answer does what was asked.

Pedro ( 2014-03-27 15:19:08 +0200 )edit

@Pedro - You are right. I somehow thought @Ram.BM wants to have all in one column with the hyphen. 2 colums makes it easier 1. colum: =left(b1,len(b1)-9 and 2. column: =right(b1,6)

@w_whalley's idea with Text to Column looks also very good.

ROSt52 ( 2014-03-27 19:09:20 +0200 )edit

add a comment

How to split data based on hyphen [closed]

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 15:53:52.170697

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

How to split data based on hyphen [closed] edit

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-02-20 15:53:52.170697

2 Answers

Comments

Comments

Question Tools

Stats

Related questions

How to split data based on hyphen [closed]

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-20 15:53:52.170697