• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 85
  • Last Modified:

Excel Formula to Pull Data from Text String

Hello -

Based on the attached example I am looking for a formula that will pull the text between the 3rd and 4th "~" in the string.  So the result from cell A1 would be alpha,  Result from cell A4 would be ZT, etc.  All results can be placed in column B.

Thanks!
0
Escanaba
Asked:
Escanaba
1 Solution
 
EscanabaAuthor Commented:
0
 
Saqib Husain, SyedEngineerCommented:
=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("<",SUBSTITUTE(A1,"~","<",3)),""),"~",REPT(" ",9999)),100))
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please try this......

In B1
=TRIM(MID(SUBSTITUTE(A1,"~",REPT(" ",LEN(A1))),LEN(A1)*3,LEN(A1)))

Open in new window

and then copy it down.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rgonzo1971Commented:
Hi,

pls try

=TRIM(MID(SUBSTITUTE(A1,"~",REPT(" ",999)),3*999,999))

Open in new window

Regards
0
 
tomfarrarCommented:
See attached.  Use the Text To Column function.  Instructions in the spreadsheet attached.
EE-Example--1-.xlsx
0
 
EscanabaAuthor Commented:
Thanks!
0
 
Saqib Husain, SyedEngineerCommented:
Thanks for the points but I do wonder why you did not consider the shorter formulas.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now