• Status: Solved
• Priority: Medium
• Security: Public
• Views: 279

# Split in R1C1

This is response to this question:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28019626.html#a38852952
There are strings in column A. What formula do I need to insert at column D and B so that column D gets the first value before the “-” and the column E gets the value after the “-”

Row      Column      Value      As      Bs

How to craft the R1C1 here?
Thanks
0
Rayne
• 7
• 6
1 Solution

Commented:
Hi Rayne,

Assuming you're working with row 2...

Column D: =LEFT(A2,FIND("-",A2,1)-1)
Column E: =MID(A2,FIND("-",A2,1)+1,999)

> What formula do I need to insert at column D and B...
I assume you mean "...D and E..."?
0

Author Commented:
Awesome Thank you :)
0

Commented:
A pleasure doing business, Rayne.  Do call again.
And thanks for the prompt payment.
0

Commented:
Rayne, you could have just used:

Add a "blank" column after the data to split.
Then Highlight just the column to split.
Then...
Data > text to column > delimited > use "-" as delimiter
0

Author Commented:
Hello All:)
You are right The_Barman - there are always two sides of the Story

tel2 - You are hilarious, seriously :D
0

Commented:
Seriously hilarious, Rayne?  That's coz I always take humour seriously.

No extra charge for the humour...this time.

Where on earth are you, anyway?

tel2 in NZ
0

Author Commented:
Thats GOLD right there, brother :D
in US
0

Author Commented:
"No extra charge for the humour...this time" :D
0

Commented:
The US?  I'm sure I've heard of that place before.  Part of the USA isn't it?

One thing I love about the US is, it's built on some strong Christian heritage.  NZ is, too.  Both places have had it pretty well eroded over the years though, I think.  Prayer outlawed in US schools, etc, and plenty of similar things here.

What's do you think is "gold" about NZ?

Just tell me if I'm getting off topic.
0

Author Commented:
hehehe- "Prayer outlawed in US schools" !!

I gotta say Kiwi -the cutest bird on mother earth (I am a bird lover) and  Chris Cairns, the greatest bowler (allrounder) I have seen from here.

You are fine, no off topic, keep it coming..:)
0

Commented:
Glad to hear this is somehow still on the topic of Excel, Rayne.

Did you think I was joking about prayer being outlawed in US schools?  You know it is, right?  I assume it's formal prayer in groups, etc that is illegal.
0

Author Commented:
hehe ...topic of excel.

I knew a bit of it. Constitutional stuff and others. But yeah things change so much down the lane and years. I think the southern part of USA like Texas etc still have stronger heritage feel, to be honest than the northern/north western part. But overall its about the same.
0

Commented:
OK.

I'm glad to report that our national anthem (which is basically a prayer) has not yet been eroded, though some have tried to replace it.  Here's the MP3 and PDF:
http://karakia.co.nz/2011/01/28/free-song-national-anthem/
Feel free to skip the first part of the MP3 which is in Maori - the original language of our native people, who now make up about 10% of the population I think.
0

Author Commented:
Hello tel2,

I did listened to it today. I really liked it. Its a very mellow, fine tune and feels different. I felt it has a holistic touch to it, feels like it touches every aspect of an entity.

Rayne
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.