• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Shorten some text strings from a list

P0lar

P0lar

Member
Hi,


I have a list of about 6000 records which are a mixture of text and numbers. the string is either: Numbers, text, numbers, text or: Text, numbers, text or: Text, numbers. I need to write a formula (or UDF) to remove the last (right hand side) text from all the records which have it.


Any help gratefully appreciated as I keep ending up in monstrous rows of nested Mid() formulas at the minute!
 
Hi ,


A couple of questions :


1. You have mentioned 3 templates viz.


Numbers text numbers text


Text numbers text


Text numbers


In the above 3 , only the first two will be processed , since the 3rd template does not end in text ; is this correct ?


If so , then after processing the above 3 templates , we will have :


Numbers text numbers


Text numbers


Text numbers


Is this correct ?


2. Is there any delimiter between the segments or are they together ? For instance :


ABCPR1234S or ABCPR 1234 S


Narayan
 
Hi,


A small smaple of the data - I'm looking to return the same list but remove the last text characters after the last number in the string so e.g. M-F9 and M-F17 etc. fro the list:


TRASU-TH13

NCFLX4

M-F9B

37.5BLANK

4WKUDD7

M-F17F

TRAMFD14

M-F14A

FM-TH12EE

M-F17A
 
Hi ,


Try this array formula , entered using CTRL SHIFT ENTER :


=LEFT(A1,IF(ISNUMBER(RIGHT(A1,1)+0),LEN(A1),MAX(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)+0),ROW(INDIRECT("1:"&LEN(A1)))))))


assuming your string is in A1.


Narayan
 
Thanks, I'm getting the message that this is using more levels of nesting than the current file format (tried in xls, xlsx and xlsm)?
 
Hi ,


I can't understand why this should come , since there are only 2 IF statements !


I tried it out with the sample data you posted , and I got correct results.


Can you upload your file , or is the data you posted not the real data that is being input to this formula ?


If you wish , I can upload my example file.


Narayan
 
Hi,


A quick check through my excel settings and I've switched on multithreaded calculations (no idea why this wasnt set up) and restarted excel after saving as .xlsm, the formula's now working I think as written...


but for e.g. 37.5BLANK it's returning just the 37.5 but I'd like to return the whole string in this case (number, string)...?
 
Hi ,


Can you explain why it should return 37.5BLANK when BLANK is text ?


Can you please specify the problem in all detail , and upload a sample workbook with the results you expect ?


Narayan
 
Thanks for the help so far Narayan,


Sadly I can't upload stuff due to IT restrictions on the network I'm posting from... The sheet is just a longer list of similar strings which were written over many years without a naming convention as you can probably tell and I'm trying to sort then into some sort of order. Where there is a letter at the end which follows a number it generally means that thre are multiple versions in the list (A, B, C, D etc. ) but only for some patterns:


Text, Number = return everything

Number, Text = return everything

Number1, Text, Number2 = return everything

Text1, Number, Text2 = return Text1, Number

Number1, Text1, Number2, Text2 = return Number1, Text1, Number2


Sadly 37.5 I'm working with is meant to be the decimal 37.5 rather than number 37, text(.), number 5


I think all this generalises to: find the last number in the string, drop any text which comes after it from the result. I probably should have written that in the inital posting now I come to think about it, sorry!
 
Hi ,


I understand everything that you have written except for how 37.5BLANK is to return not just 37.5 but 37.5BLANK ; this is now clear , since you mention that if there is only one number followed by text , then return everything.


The 5 patterns you have posted , are they the only possible patterns or can there be more ?


Narayan
 
Hi ,


Sorry , but it's late tonight , and I can only leave you with some ideas ; I can post the complete solution only tomorrow.


First , let us make a table as follows :

[pre]
Code: 
Input String     Transition from Text to Numbers     Transition from Numbers to Text

Text                            0                                    0
Numbers                         0                                    0
TextNumbers                     1                                    0
TextNumbersText                 1                                    1
TextNumbersTextNumbers          2                                    1
NumbersText                     0                                    1
NumbersTextNumbers              1                                    1
NumbersTextNumbersText          1                                    2
[/pre]
The formulae for detecting the transitions are as follows :


From Text To Numbers

=SUM(IF(NOT(ISNUMBER(MID(SUBSTITUTE(A1,".",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,".",""))-1)),1)*1))*ISNUMBER(MID(SUBSTITUTE(A1,".",""),ROW(INDIRECT("2:"&LEN(SUBSTITUTE(A1,".","")))),1)*1),1,0))


From Numbers To Text

=SUM(IF(ISNUMBER(MID(SUBSTITUTE(A1,".",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,".",""))-1)),1)*1)*NOT(ISNUMBER(MID(SUBSTITUTE(A1,".",""),ROW(INDIRECT("2:"&LEN(SUBSTITUTE(A1,".","")))),1)*1)),1,0))


Both formulae are entered as array formulae , using CTRL SHIFT ENTER.


Using these results , and seeing whether the last character on the right is a number or not will tell us what is to be done.


Narayan


P.S. These formulae have been copied from here :


http://www.excelforum.com/excel-formulas-and-functions/892003-detecting-transitions-between-alpha-numeric-characters.html
 
Fantastic,


Looks like these will split out the bits I'd need to get this working, thanks for the help!
 
You must log in or register to reply here.
Back
Top