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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
114
Peter_SSs
Peter_SSs
T
  • Question
Remove Duplicates with Dynamic Columns Array
Replies
0
Views
275
trough
T
E
  • Question
Customized text wrapping to new row (not within cell)
Replies
8
Views
177
rlv01
R
L
  • Question
Return text from cell in column containing text, as long as values in other columns match several criteria
Replies
7
Views
332
etaf
etaf
D
  • Solved
Formula to aggregate emails if values in other cells match certain text
Replies
3
Views
230
Marcelo Branco
M

Forum statistics

Threads
1,214,385
Messages
6,119,210
Members
448,874
Latest member
b1step2far

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