Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 87
  • 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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