Extract Text

How_Do_I

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,843
Office Version
  1. 2010
Platform
  1. Windows
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Can anyone help me sort this please?
<o:p> </o:p>
My formula isn’t working as the “v” in <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:City w:st="on"><st1:place w:st="on">Coventry</st1:place></st1:City> is being found.
<o:p> </o:p>
Also in Column D I want just the name after the “v” i.e. Brighton, <st1:place w:st="on"><st1:PlaceName w:st="on">Bristol</st1:PlaceName> <st1:PlaceType w:st="on">City</st1:PlaceType> <st1:PlaceName w:st="on">etc</st1:PlaceName></st1:place>… So it is any text after the “v” and before the “,”…
<o:p> </o:p>
Any ideas please?

Excel Workbook
ABCD
2Brighton v Doncaster, 15:00BrightonDoncaster, 15:00
3Bristol City v Ipswich, 15:00Bristol CityIpswich, 15:00
4Burnley v Watford, 15:00BurnleyWatford, 15:00
5Coventry v Leicester, 15:00Cntry v Leicester, 15:00
Sheet1
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Sort by date Sort by votes
How_Do_I said:
Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Can anyone help me sort this please?
<o:p></o:p>
My formula isn’t working as the “v” in <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:City w:st="on"><st1:place w:st="on">Coventry</st1:place></st1:City> is being found.
<o:p></o:p>
Also in Column D I want just the name after the “v” i.e. Brighton, <st1:place w:st="on"><st1:PlaceName w:st="on">Bristol</st1:PlaceName> <st1:PlaceType w:st="on">City</st1:PlaceType> <st1:PlaceName w:st="on">etc</st1:PlaceName></st1:place>… So it is any text after the “v” and before the “,”…
<o:p></o:p>
Any ideas please?

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 234px"><COL style="WIDTH: 64px"><COL style="WIDTH: 92px"><COL style="WIDTH: 137px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Brighton v Doncaster, 15:00</TD><TD> </TD><TD>Brighton</TD><TD>Doncaster, 15:00</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Bristol City v Ipswich, 15:00</TD><TD> </TD><TD>Bristol City</TD><TD>Ipswich, 15:00</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Burnley v Watford, 15:00</TD><TD> </TD><TD>Burnley</TD><TD>Watford, 15:00</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Coventry v Leicester, 15:00</TD><TD> </TD><TD>C</TD><TD>ntry v Leicester, 15:00</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>=LEFT(A2,FIND("v",A2)-2)</TD></TR><TR><TD>D2</TD><TD>=MID(A2,FIND("v",A2)+2,LEN(A2))</TD></TR><TR><TD>C3</TD><TD>=LEFT(A3,FIND("v",A3)-2)</TD></TR><TR><TD>D3</TD><TD>=MID(A3,FIND("v",A3)+2,LEN(A3))</TD></TR><TR><TD>C4</TD><TD>=LEFT(A4,FIND("v",A4)-2)</TD></TR><TR><TD>D4</TD><TD>=MID(A4,FIND("v",A4)+2,LEN(A4))</TD></TR><TR><TD>C5</TD><TD>=LEFT(A5,FIND("v",A5)-2)</TD></TR><TR><TD>D5</TD><TD>=MID(A5,FIND("v",A5)+2,LEN(A5))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Click to expand...
Try these:

=LEFT(A2,FIND(" v ",A2)-1)

=MID(A2,FIND(" v ",A2)+3,50)
 
Upvote 0
Hi Biff,

Thanks for that... I take it that I need to shoot the formula down in C1 to make Column B work... Also, I need to lose the , and the time in Column C.

So this is what I'm after for my return...

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 234px"><COL style="WIDTH: 250px"><COL style="WIDTH: 137px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Brighton v Doncaster, 15:00</TD><TD style="FONT-FAMILY: Verdana">Brighton</TD><TD style="FONT-FAMILY: Verdana">Doncaster</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Bristol City v Ipswich, 15:00</TD><TD style="FONT-FAMILY: Verdana">Bristol City</TD><TD style="FONT-FAMILY: Verdana">Ipswich</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Burnley v Watford, 15:00</TD><TD style="FONT-FAMILY: Verdana">Burnley</TD><TD style="FONT-FAMILY: Verdana">Watford</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Coventry v Leicester, 15:00</TD><TD style="FONT-FAMILY: Verdana">Coventry</TD><TD style="FONT-FAMILY: Verdana">Leicester</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 
Upvote 0
Messed post #4 up... That was a reply to HOTPEPPER...

Biff, I'll try your suggestion now. Left of the "v" is working, the second formula is pulling the ", 15:00" etc which I don't want. Can that be sorted please?

Thanks
 
Last edited:
Upvote 0
Using your posted example, try this...
Code: 
C1: =LEFT(A1,SEARCH(" V ",A1)-1)
D1: =LEFT(SUBSTITUTE(A1,C1&" v ",""),FIND(",",SUBSTITUTE(A1,C1&" v ",""))-1)

C1 will return: Brighton
D1 will return: Doncaster

Copy those formulas down as far as you need.

Is that something you can work with?
 
Upvote 0
How_Do_I said:
Hi Biff,

Thanks for that... I take it that I need to shoot the formula down in C1 to make Column B work... Also, I need to lose the , and the time in Column C.

So this is what I'm after for my return...

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 234px"><COL style="WIDTH: 250px"><COL style="WIDTH: 137px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>Brighton v Doncaster, 15:00</TD><TD style="FONT-FAMILY: Verdana">Brighton</TD><TD style="FONT-FAMILY: Verdana">Doncaster</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>Bristol City v Ipswich, 15:00</TD><TD style="FONT-FAMILY: Verdana">Bristol City</TD><TD style="FONT-FAMILY: Verdana">Ipswich</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>Burnley v Watford, 15:00</TD><TD style="FONT-FAMILY: Verdana">Burnley</TD><TD style="FONT-FAMILY: Verdana">Watford</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Coventry v Leicester, 15:00</TD><TD style="FONT-FAMILY: Verdana">Coventry</TD><TD style="FONT-FAMILY: Verdana">Leicester</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
Click to expand...
Try these...

Book1
ABC
2Brighton v Doncaster, 15:00BrightonDoncaster
3Bristol City v Ipswich, 15:00Bristol CityIpswich
4Burnley v Watford, 15:00BurnleyWatford
5Coventry v Leicester, 15:00CoventryLeicester
Sheet2

Formula entered in B2 and copied down:

=LEFT(A2,FIND(" v ",A2)-1)

Formula entered in C2 and copied down:

=LEFT(MID(A2,FIND(" v ",A2)+3,50),FIND(",",MID(A2,FIND(" v ",A2)+3,50))-1)
 
Upvote 0
Ron, beautiful thanks…

Biff, beautiful thanks...

I’m spoilt for choice now…:)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Thanks too HOTPEPPER…:)
 
Upvote 0
How_Do_I said:
Ron, beautiful thanks…

Biff, beautiful thanks...

I’m spoilt for choice now…:)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Thanks too HOTPEPPER…:)
Click to expand...
Glad I could help. Thanks for the feedback! :cool:
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Reference UK Post Code Column to Create and print Country from a reference table? IF formula or VLookup?
Replies
5
Views
672
Fluff
Fluff
P
  • Question Question
Identify last 5 occurences in a list of multiple columns
Replies
5
Views
536
Peter_SSs
Peter_SSs
A
  • Question Question
Print Area and Printing
Replies
3
Views
1K
pbornemeier
pbornemeier
R
  • Question Question
change cell based on selected cell
Replies
5
Views
652
John Davis
J
M
  • Question Question
Formula query - lookup function
Replies
3
Views
600
TMShucks
TMShucks

Forum statistics

Threads
1,224,586
Messages
6,179,729
Members
452,939
Latest member
WCrawford

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