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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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

J
  • Question Question
Extracting number from text string
Replies
4
Views
512
Fluff
Fluff
AOB
  • Solved
Extract the URL and display text of a HTML hyperlink tag in a simple string variable
Replies
2
Views
242
AOB
AOB
W
  • Solved
Extract the characters between 2 spaces in a string
Replies
5
Views
1K
Fluff
Fluff
A
  • Question Question
Excel Function to Extract Text String, Remove Duplicate & Count
Replies
5
Views
280
arijitirf
A
P
  • Question Question
Inserting carriage returns into text strings of varying lengths
Replies
1
Views
259
Cubist
Cubist

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

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