Ask Your Question

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

VADODARA - 390010
CHENNAI -600042

I need output like

columnA       columnB
VADODARA      390010
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

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


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

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


@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


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

Seen: 791 times

Last updated: Mar 27 '14