Improve company productivity with a Business Account.Sign Up

x
?
Solved

Split in R1C1

Posted on 2013-02-04
14
Medium Priority
?
278 Views
Last Modified: 2013-02-09
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
Comment
Question by:Rayne
  • 7
  • 6
14 Comments
 
LVL 12

Accepted Solution

by:
tel2 earned 2000 total points
ID: 38853006
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 Closing Comment

by:Rayne
ID: 38853025
Awesome Thank you :)
0
 
LVL 12

Expert Comment

by:tel2
ID: 38853038
A pleasure doing business, Rayne.  Do call again.
And thanks for the prompt payment.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 24

Expert Comment

by:Steve
ID: 38853044
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 Comment

by:Rayne
ID: 38853710
Hello All:)
You are right The_Barman - there are always two sides of the Story

tel2 - You are hilarious, seriously :D
0
 
LVL 12

Expert Comment

by:tel2
ID: 38853716
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 Comment

by:Rayne
ID: 38853727
Thats GOLD right there, brother :D
in US
0
 

Author Comment

by:Rayne
ID: 38853728
"No extra charge for the humour...this time" :D
0
 
LVL 12

Expert Comment

by:tel2
ID: 38854149
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 Comment

by:Rayne
ID: 38858140
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
 
LVL 12

Expert Comment

by:tel2
ID: 38858161
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 Comment

by:Rayne
ID: 38858205
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
 
LVL 12

Expert Comment

by:tel2
ID: 38861760
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 Comment

by:Rayne
ID: 38871507
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
While repairing a PST file with ScanPST.exe, many users have encountered the error "Attempting to Validate BBT Refcounts". In this article, we are going to describe the reasons and solution to fix the error.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question