extracting text between 2 points in a large string

lesray

New Member
Joined
Oct 23, 2014
Messages
2
I am trying to extract the manes from a large string and place them into column B from column D. the problem i am having as the only unique identifier to pick from @ comes after the name and as such i can't seem to extract the name SmithJ*** the strings i have are around 700 chars and therefore lots of the <> are repeated
Here is a sample. the 2 identifiers are in bold. Each string may be slightly different but they will all contain the @ symbol after the name and the > before the name. then i would like to place those names into column B
I am happy for either VBA or a formula. Screenshot 2022-01-06 171624.png
wdwhnkljnwef<>fgf4rtsdgsger<>qwdqwefwrf<+=xxvcdsvwdfgerg,>wdfwerf"dfgbfhrgh"vbbg>vbedgfg)"true">SmithJ120@me.comfwfihfrf>,mdgegergerg< Value=<>edfegTest"fggnfgn"
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
The length of your text coupled with the number of greater than symbols may push the limits of this formula, but give it a try...
Excel Formula:
=TRIM(RIGHT(SUBSTITUTE(LEFT(D1,FIND("@",D2)-1),">",REPT(" ",900)),900))
 
Upvote 0
The length of your text coupled with the number of greater than symbols may push the limits of this formula, but give it a try...
Excel Formula:
=TRIM(RIGHT(SUBSTITUTE(LEFT(D1,FIND("@",D2)-1),">",REPT(" ",900)),900))
it worked perfectly, after i adjusted the D1 to D2 I just need to try it on the valid data in the morning. thanks
 
Upvote 0
Another option

Excel Formula:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(CHAR(34)&">",A1)+2,99),"@",REPT(" ",99)),99))
 
Upvote 0
Hi to all.
With a macro:
VBA Code:
Option Explicit
Sub ExtractText()
    Dim lr, x, position, count, lng
    lr = Range("D" & Rows.count).End(xlUp).Row
    For x = 2 To lr
        position = InStr(1, Range("D" & x), "@", vbTextCompare)
        count = position
        Do
            count = count - 1
        Loop Until InStr(count, Left(Range("D" & x), position - 1), ">", vbTextCompare) <> 0
        lng = position - count - 1
        Range("B" & x) = Mid(Range("D" & x), count + 1, lng)
    Next x
End Sub
 
Upvote 0
Or another
Excel Formula:
=REPLACE(LEFT(D2,FIND("@",D2)-1),1,FIND(""">",D2)+1,"")
 
Upvote 0
For a VBA solution, here is another (non-looping) one you can consider...
VBA Code:
Sub GetNames()
  Columns("B").Value = Columns("D").Value
  Columns("B").Replace "@*", "", xlPart
  Columns("B").Replace "*>", "", xlPart
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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