Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-11-2017, 05:46 AM
Haha88 Haha88 is offline Extract text from a text string Windows 8 Extract text from a text string Office 2010 32bit
Advanced Beginner
Extract text from a text string
 
Join Date: Mar 2015
Posts: 51
Haha88 is on a distinguished road
Default Extract text from a text string

Hi,

I'm looking for a formula which helps me to extract text from a text string.

ie:

GBGD.6251.700001.netpay
GBGD.6251.700001.paye


GBGD.6251.700001.NI
GBGD.6251.700008.pension contribution
GBGD.6251.700020.EER
GBGD.6251.700021.EEE
And I'd like to extract only 700001,700008,700020& 700021.
I've looked at MID,LEN ect but not sure how i can use those formula for this purpose.

Can someone help me?
I also attached the Excel example.

Many thanks
Attached Files
File Type: xlsx Book1.xlsx (8.7 KB, 11 views)
Reply With Quote
  #2  
Old 02-11-2017, 06:30 AM
xor xor is offline Extract text from a text string Windows 10 Extract text from a text string Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,080
xor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of lightxor is a glorious beacon of light
Default

With your data in A2:B7 enter in C2:

=--MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,FIND(".",A2,F IND(".",A2,FIND(".",A2)+1)+1)-FIND(".",A2,FIND(".",A2)+1)-1)

Copy down.
Reply With Quote
  #3  
Old 02-11-2017, 08:01 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Extract text from a text string Windows 7 64bit Extract text from a text string Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,584
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

Or
Data
Text to columns
Fixed width
Insert break lines
In the last window select "Do not import" for the unnecessary columns
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 02-11-2017, 09:10 AM
jeffreybrown jeffreybrown is offline Extract text from a text string Windows Vista Extract text from a text string Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Or

=TRIM(LEFT(RIGHT(SUBSTITUTE(D4,".",REPT(" ",100)),200),100))
Reply With Quote
  #5  
Old 02-11-2017, 10:54 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Extract text from a text string Windows 7 64bit Extract text from a text string Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,584
Pecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to beholdPecoflyer is a splendid one to behold
Default

There's more than one way to skin a cat
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #6  
Old 02-11-2017, 11:20 AM
jeffreybrown jeffreybrown is offline Extract text from a text string Windows Vista Extract text from a text string Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Quote:
Originally Posted by Pecoflyer View Post
There's more than one way to skin a cat
All the more reason they have nine lives....
Reply With Quote
  #7  
Old 02-13-2017, 04:44 PM
Haha88 Haha88 is offline Extract text from a text string Windows 8 Extract text from a text string Office 2010 32bit
Advanced Beginner
Extract text from a text string
 
Join Date: Mar 2015
Posts: 51
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by xor View Post
With your data in A2:B7 enter in C2:

=--MID(A2,FIND(".",A2,FIND(".",A2)+1)+1,FIND(".",A2,F IND(".",A2,FIND(".",A2)+1)+1)-FIND(".",A2,FIND(".",A2)+1)-1)

Copy down.
This works the wonder!!!

Just wondering why "--"?

Many thanks
Reply With Quote
  #8  
Old 02-13-2017, 04:50 PM
jeffreybrown jeffreybrown is offline Extract text from a text string Windows Vista Extract text from a text string Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

It's called a double unary and it's used to convert true/false to 1/0

This may be a good place to start with an explanation...

Double Unary
Reply With Quote
  #9  
Old 02-13-2017, 05:06 PM
Haha88 Haha88 is offline Extract text from a text string Windows 8 Extract text from a text string Office 2010 32bit
Advanced Beginner
Extract text from a text string
 
Join Date: Mar 2015
Posts: 51
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
Or
Data
Text to columns
Fixed width
Insert break lines
In the last window select "Do not import" for the unnecessary columns
Thank you for this. I tried but dont think it will work as will delete the original data

Many thanks
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract text from a text string Extract a string from a paragraph kirkm Word VBA 7 09-11-2016 06:13 PM
Extract text from a text string Way to search for a string in text file, pull out everything until another string? omahadivision Excel Programming 12 11-23-2013 12:10 PM
How to extract only numbers from a STRING? Learner7 Excel 3 07-02-2013 06:25 AM
Extract numbers from a text string aleale97 Excel 4 02-10-2011 10:33 AM
Extract from String using Wildcard whousedmy Word 0 05-21-2009 01:35 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:40 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2022, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2022 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft