Ask Your Question
0

How to split data based on hyphen [closed]

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

Ram.BM gravatar image

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

Pedro gravatar image

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

2 Answers

Sort by » oldest newest most voted
1

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

w_whalley gravatar image

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

Pedro gravatar image

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 more

Comments

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

Ram.BM gravatar imageRam.BM ( 2014-03-28 07:28:13 +0100 )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 gravatar imagePedro ( 2014-03-28 17:32:04 +0100 )edit
0

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

ROSt52 gravatar image

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 more

Comments

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

Pedro gravatar imagePedro ( 2014-03-27 15:19:08 +0100 )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 gravatar imageROSt52 ( 2014-03-27 19:09:20 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2014-03-27 13:53:28 +0100

Seen: 791 times

Last updated: Mar 27 '14