VBA: Extracting text between 2 strings in a string....

RobbieC

RobbieC

Active Member
Joined
Dec 14, 2016
Messages
376
Office Version
  1. 2010
Platform
  1. Windows
Hi there, I have a worksheet and in column B, there is a large string (html code):

HTML: 
St Withburga Lane, , Dereham , NR19 1EDProject No: DIL029-Dereham-123456Client: Acme Engineering
Number of files: 4

In here is an address, project no, client & number of files. I need to extract each of these bits. I can see that the 'address' is sandwiched between:

HTML: 
and 

Project No:

and similarly for the other 3 strings...

How can I get the text in between these 2 strings?

If you can point me in the right direction, I'd be most grateful. Thanks
 
Last edited by a moderator:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
Hi,

Adjust to your needs ...Activecell is the field which is holding the whole string ...

Code: 
str = Left(ActiveCell, Application.WorksheetFunction.Find("Project", ActiveCell))

Hope this will help
 
Upvote 0
My code did not display properly - it removed all the important stuff... doh

how can I post up bits of html in this post without it converting the code?

I tried wrapping it in html tags, but the same as above happens. Looks fine in 'preview' but then the <html> code disappears
 
Last edited:
Upvote 0
PHP: 
<div style=\"font-size:16px;\"><div>St Withburga Lane, , Dereham , NR19 1ED<div><strong>Project No: </strong>DIL029-Dereham-123456<div><strong>Client: </strong>Acme Engineering</strong><br /><strong>Number of files:</strong> 4<div></div></div></div></div>

so I need to get the string between:


PHP: 
<div style=\"font-size:16px;\"><div>

and


PHP: 
<div><strong>Project
 
Upvote 0
So, I just managed to get the FULL string above by wrapping it in PHP tags....
 
Upvote 0
Hi again,

The structure has to be replicated along this formula

Code: 
=MID(A1,FIND(">",A1)+6,(FIND("Project",A1)-13)-(FIND(">",A1)+6))

HTH
 
Upvote 0
Code snipped continues to disappear.
Disregard this post
 
Last edited:
Upvote 0
Where Cell A1 has your data.
Adjust/Change where required.

In order for it to show up here, I added a bunch of spaces. Obviously, they need to be deleted if you copy/paste.
Code: 
MsgBox Trim(Mid(Range("A1").Value, Len(" < div style=\"), InStr(Range("A1").Value, " < div > < strong > Project")))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Extract the characters between 2 spaces in a string
Replies
5
Views
207
Fluff
Fluff
E
  • Question
Extract texts between []
Replies
7
Views
271
eduzs
E
C
  • Question
Extract Address information from large text file
Replies
5
Views
425
hayden
H
G
  • Solved
Match Number, then Find a string and extract the data between the brackets.
Replies
7
Views
445
Peter_SSs
Peter_SSs
E
  • Solved
Extract variable length string between first and last spaces
Replies
6
Views
1K
ExceL0ver
E

Forum statistics

Threads
1,215,001
Messages
6,122,648
Members
449,092
Latest member
peppernaut

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