Seperate text into 2 columns

A

allanTeh

New Member
Joined
Jun 29, 2006
Messages
43
I keep sport database. In Column 'A', I have this text:

San Francisco v New York

I want to create 2 columns:-

In one Column (lets say Column 'B') to keep the FIRST PART of the text (of Column 'A'), and that is:- San Francisco

In next Column (lets say Column 'C') to keep the SECOND PART of the text (of Column 'A'), and that is:- New York

Problem: What is the function/formula I should type in Columns B and C to make it happen ?

THANKS !
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Hi,

If there are always a " v " between the two texts,

In B2,

=LEFT(A2,FIND(" v",A2&" v"))

In C2,

=SUBSTITUTE(A2,B2&"v ","")

HTH
 
Upvote 0
allanTeh

You could also try this:
1. Select column A by clicking its heading label.
2. Data|Text to Columns...|Delimited|Next|select 'Other' and type a v in the 'Other' box|Next|Put B1 in the 'Destination' box|Finish

Note however that this would leave a space after San Francisco and a space before New York and this may be unsatisfactory for you.
 
Upvote 0
Hi Mr KrishnaKumar,

Thank You for the formulas you supplied, although I don't understand those completely, I just applied them and they worked for me.

Once again, a big THANK YOU.!

Thanks also to Mr Peter although I don't know what's the vertical lines means (in his no.2 sentence). Thanks for the passion to help!

allanTeh
Singapore
 
Upvote 0
... although I don't know what's the vertical lines means (in his no.2 sentence).
Click to expand...
It means:
Click the Data menu at the top of the Excel screen,
Then click 'Text to Columns...' from the menu that drops down,
Then choose 'delimited' from the next screen that appears,
etc
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Average cells based on a date criteria, with blanks cells in various rows...
Replies
5
Views
383
Peter_SSs
Peter_SSs
M
  • Question Question
Using text input box to call cell values.
2
Replies
12
Views
312
Skyybot
Skyybot
gtd526
  • Question Question
Match Values in a different worksheet (odd rows)
Replies
0
Views
157
gtd526
gtd526
J
  • Question Question
How to remove variably placed text within a string of text by looking at the columns to the right
Replies
1
Views
148
Alex Blakenburg
A
S
  • Question Question
Project Status Spreadsheet
Replies
6
Views
208
Fluff
Fluff

Forum statistics

Threads
1,225,476
Messages
6,185,209
Members
453,283
Latest member
Shortm88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top