Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 04-05-2019, 12:00 PM
14spar15 14spar15 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Mar 2011
Posts: 39
14spar15 is on a distinguished road
Default Formula to extract last two letters from each word


Hello, I have a cell that has a list of years (example 1999 2000 2001 2002 2003 2004) and I need a formula to change this example into (99 00 01 02 03 04). There may be anywhere from one year up to 20 years in this cell. I found this

Code:
=LEFT(A1,1)&MID(A1,FIND(" ",A1&" ",1)+1,1)&MID(A1&"  ",FIND(" ",A1&"  ",FIND(" ",A1&" ",1)+1)+1,1)
that I was going to modify (It uses first letter of each word) but I am finding out it is only going to work for up to 3 years. I guess I could draw it out to 20 words/numbers but it seems there is probably a better way. Any ideals here? Thank-You
Reply With Quote
  #2  
Old 04-05-2019, 11:47 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,007
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default Formula to extract last two letters from each word

See cell B1 in the attached.
As you can see the solution requires helper cells (as C1:V1). If you so wish these can be moved to somewhere out of sight.

Edit:
Only now I see that you use Office 2010 so I guess you can't use above solution as it requires the function TEXTJOIN which is only available in the most recent version of Excel.
Attached Files
File Type: xlsx Extract_Last_Two.xlsx (9.7 KB, 5 views)
Reply With Quote
  #3  
Old 04-06-2019, 07:59 AM
14spar15 14spar15 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Mar 2011
Posts: 39
14spar15 is on a distinguished road
Default

Hello, I appreciate you taking the time to help here. It did get me thinking in another direction through. Maybe if I extract each year into its own cell, trim the first two numbers off each and put these new values back together. Might it be better to modify the formula in my original post to extract the last 2 numbers and string out the capacity to do up to 20 words. I have tried this but I don't have enough understanding of the formula to do this. Just a note: each original number will always have 4 characters and they will always be numbers. Thanks again..
Reply With Quote
  #4  
Old 04-06-2019, 08:19 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 676
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

If you can live with an xlsm file,
how about a User Defined Function (UDF) ?
Code:
Public Function LastTwo(Orig As String) As String
    Dim i As Long, arr
    arr = Split(Orig, " ")
    For i = LBound(arr) To UBound(arr)
        LastTwo = Trim(LastTwo & " " & Right(arr(i), 2))
    Next i
End Function
Attached Files
File Type: xlsm Extract_Last_Two.xlsm (14.8 KB, 8 views)
Reply With Quote
  #5  
Old 04-06-2019, 08:25 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,367
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Quote:
It did get me thinking in another direction through. Maybe if I extract each year into its own cell, trim the first two numbers off each and put these new values back together. Might it be better to modify the formula in my original post to extract the last 2 numbers and string out the capacity to do up to 20 words.
Yes, but it would be very cumbersome, even using Text to columns .
And to put the results together you would have something like =A1&" "&A2&" "....
The UDF NoS provided would be easier ( haven't tested it but I suppose he/she did )
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #6  
Old 04-06-2019, 09:52 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,007
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default Formula to extract last two letters from each word

I think NoSparks solution is unbeatable

If however you prefer no VBA you might be interested in the attached.
Attached Files
File Type: xlsx Extract_Last_Two_2.xlsx (9.5 KB, 7 views)
Reply With Quote
  #7  
Old 04-06-2019, 10:37 AM
14spar15 14spar15 is offline Windows 7 64bit Office 2010 64bit
Advanced Beginner
 
Join Date: Mar 2011
Posts: 39
14spar15 is on a distinguished road
Default

Worked like a charm. Already using xlsm so this fit right in and this is so simple. Maybe "simple" is the wrong word because I can't figure out how it works. Very short and compact. It's something else I can study when I get caught up here. Many Thanks..
Reply With Quote
  #8  
Old 04-06-2019, 12:02 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 676
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

@ xor
I'm no good with formulas, but I think you need to re-visit populating C1 to V1 as it misses the second year shown in A1
Reply With Quote
  #9  
Old 04-06-2019, 11:32 PM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,007
xor is just really nicexor is just really nicexor is just really nicexor is just really nicexor is just really nice
Default

@NoSparks

... populating C1 to V1 as it misses the second year shown in A1 ....

You are right.
The formula in C1 should just be copied to D1:V1 and it works. I don't know what has happened.

Last edited by xor; 04-07-2019 at 01:44 AM.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to Extract text from a text string Haha88 Excel 2 11-14-2017 01:32 AM
How to assign numbers to letters and then create a formula to add them together for a rota Mskapri Excel 6 04-01-2017 03:05 PM
I'm looking for a formula to extract all the text before the last name in a cell Gef Excel 4 02-01-2015 07:14 PM
Word not 'rounding' letters up to word in next line (parents and orphans) Asa Word 4 09-13-2014 06:47 PM
extract text with formula s7y Excel 7 06-05-2013 06:18 AM


All times are GMT -7. The time now is 08:49 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft