Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Retrieve characters after nth occurence of a string Retrieve characters after nth occurence of a string
Register FAQ

Reply
 
Thread Tools Display Modes
  #1  
Old 06-12-2014, 02:03 PM
veedee veedee is offline Retrieve characters after nth occurence of a string Mac OS X Retrieve characters after nth occurence of a string Office for Mac 2011
Novice
Retrieve characters after nth occurence of a string 		 
Join Date: Jun 2014
Posts: 3
veedee is on a distinguished road
Default Retrieve characters after nth occurence of a string
Hi,



I have this data in my rows:

1. BBY TOYS SJC 15
2. BBY TOYS CUP 25
3. BBY TOYS SKU(15X10)10
4. SKU BOX 1000
5. SKU BOX 10000

I need to dynamically retrieve the following from each:

1. 15
2. 25
3. 10
4. 1000
5. 10000

I used the following formula:
=RIGHT(<CellNo>,LEN(<CellNo>)-FIND(" ",<CellNo>,10))

It works for all the above rows except the 3rd, for which it retrieves: SKU(15X10)10
Can you please help? Is there a better formula that I can use?

Thanks
Reply With Quote
  #2  
Old 06-12-2014, 05:37 PM
macropod's Avatar
macropod macropod is offline Retrieve characters after nth occurence of a string Windows 7 32bit Retrieve characters after nth occurence of a string Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,963
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
You could use the following array formula (input with Ctrl-Shift-Enter):
=IF(ISERROR(RIGHT(MID(A1,MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),"")),LEN(A1)-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),""))+1),LEN(A1)-FIND(")",A1))),MID(A1,MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),"")),LEN(A1)-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),""))+1),RIGHT(MID(A1,MIN(IF(IS NUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),"")),LEN(A1)-MIN(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIR ECT("1:"&LEN(A1)))),""))+1),LEN(A1)-FIND(")",A1)))
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 06-13-2014, 12:36 PM
whatsup whatsup is offline Retrieve characters after nth occurence of a string Windows 7 64bit Retrieve characters after nth occurence of a string Office 2010 32bit
Competent Performer
  
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default
Paul, wondering if there's a shorter way?

If it's just about to separate numbers from text on the right side:
=VALUE(RIGHT(A2,SUM(--ISNUMBER(--RIGHT(A2,COLUMN(INDIRECT("1:1")))))))
Change the function RIGHT to LEFT to separate numeric parts on the left side of text.

Edit: Of course it's an arrayformula as well
Reply With Quote
  #4  
Old 06-16-2014, 03:10 AM
macropod's Avatar
macropod macropod is offline Retrieve characters after nth occurence of a string Windows 7 32bit Retrieve characters after nth occurence of a string Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,963
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
Much better!
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 06-16-2014, 12:44 PM
gebobs gebobs is offline Retrieve characters after nth occurence of a string Windows 7 64bit Retrieve characters after nth occurence of a string Office 2010 64bit
Expert
  
Join Date: Mar 2014
Location: Atlanta
Posts: 837
gebobs has a spectacular aura aboutgebobs has a spectacular aura about
Default
What do the double dashes as in "--ISNUMBER" do?
Reply With Quote
  #6  
Old 06-16-2014, 03:41 PM
whatsup whatsup is offline Retrieve characters after nth occurence of a string Windows 7 64bit Retrieve characters after nth occurence of a string Office 2010 32bit
Competent Performer
  
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default
gebobs, the double minus signs (or double dashes) just replace a multiplication by one.
That way the formula just does the same:
=VALUE(RIGHT(A2,SUM(1*ISNUMBER(1*RIGHT(A2,COLUMN(INDIRECT("1:1")))))))
Last edited by whatsup; 06-16-2014 at 07:31 PM.
Reply With Quote
Reply

« Previous Thread | Next Thread »


Similar Threads
Thread Thread Starter Forum Replies Last Post
Retrieve characters after nth occurence of a 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
Retrieve PPS files uncledewey PowerPoint 2 08-10-2012 09:08 AM
Retrieve characters after nth occurence of a string Can I retrieve a deleted folder originale Outlook 1 07-25-2011 07:51 AM
outlook retrieve messages...... ranjit_dutt Outlook 0 06-19-2010 07:35 AM
Junk characters (box-like characters) in Word file Sashikala Word 1 04-20-2010 02:03 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:36 PM.

Contact Us - Privacy Statement - Top

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