• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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
abc-sad      p1      25912      abc      sad


How to craft the R1C1 here?
Thanks
0
Rayne
Asked:
Rayne
  • 7
  • 6
1 Solution
 
tel2Commented:
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
 
RayneAuthor Commented:
Awesome Thank you :)
0
 
tel2Commented:
A pleasure doing business, Rayne.  Do call again.
And thanks for the prompt payment.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
SteveCommented:
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
 
RayneAuthor Commented:
Hello All:)
You are right The_Barman - there are always two sides of the Story

tel2 - You are hilarious, seriously :D
0
 
tel2Commented:
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
 
RayneAuthor Commented:
Thats GOLD right there, brother :D
in US
0
 
RayneAuthor Commented:
"No extra charge for the humour...this time" :D
0
 
tel2Commented:
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
 
RayneAuthor 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
 
tel2Commented:
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
 
RayneAuthor 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
 
tel2Commented:
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
 
RayneAuthor 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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now