Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
space from right
HI all, has anyone got a formula for finding thwe 1st space from the right of
a string e.g. These can have variable lengths as they are all addresses Gar 29 Durban Avenue Dalmuir West C I want to find the space between West and C Thanks all (again) -- delmac |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
space from right
Hi,
I may have made hard work of this, lets see if a simpler formula arrives =LEN(A1)-LEN(RIGHT(A1,LEN(A1)-SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))) Mike "delmac" wrote: HI all, has anyone got a formula for finding thwe 1st space from the right of a string e.g. These can have variable lengths as they are all addresses Gar 29 Durban Avenue Dalmuir West C I want to find the space between West and C Thanks all (again) -- delmac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
space from right
You posted to the Programming forum, that's why I suppose you are looking for
a VBA function. It's the InstrRev Regards, Stefi €˛delmac€¯ ezt Ć*rta: HI all, has anyone got a formula for finding thwe 1st space from the right of a string e.g. These can have variable lengths as they are all addresses Gar 29 Durban Avenue Dalmuir West C I want to find the space between West and C Thanks all (again) -- delmac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
space from right
A couple of questions. First off, are you asking for a worksheet formula
solution or a VB solution? Second, what is the goal of looking for that last space... in order to find the last "word" in the line? If so, that would be the better question to ask... in other words, tell us what you are ultimately trying to accomplish as there *may* (no guarantee) be a better way to do it than the one you have in mind. -- Rick (MVP - Excel) "delmac" wrote in message ... HI all, has anyone got a formula for finding thwe 1st space from the right of a string e.g. These can have variable lengths as they are all addresses Gar 29 Durban Avenue Dalmuir West C I want to find the space between West and C Thanks all (again) -- delmac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
space from right
Public Function LastOfThese(LineOfText$, Character$) As Integer
' Finds the position of the last time the Character appears in the LineOfText ' e.g. "0123456,789,012,345,67,8,9,000" and "," gives 27 ' It returns Zero if the Character does not appear in the LineOfText Dim N As Integer, C As Integer LastOfThese = 0 For N = 1 To Len(LineOfText$) If Mid$(LineOfText$, N, 1) = Character$ Then LastOfThese = N Next N End Function -- Regards Robin "delmac" wrote: HI all, has anyone got a formula for finding thwe 1st space from the right of a string e.g. These can have variable lengths as they are all addresses Gar 29 Durban Avenue Dalmuir West C I want to find the space between West and C Thanks all (again) -- delmac |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
space from right
Look up the InStrRev function in the help files... it will produce much more
efficient code... Function LastOfThese(LineOfText As String, Character As String) As Long LastOfThese = InStrRev(LineOfText, Character) End Function -- Rick (MVP - Excel) "Robin Clay" <Robin_B DOT Clay AT virgin DOT net wrote in message ... Public Function LastOfThese(LineOfText$, Character$) As Integer ' Finds the position of the last time the Character appears in the LineOfText ' e.g. "0123456,789,012,345,67,8,9,000" and "," gives 27 ' It returns Zero if the Character does not appear in the LineOfText Dim N As Integer, C As Integer LastOfThese = 0 For N = 1 To Len(LineOfText$) If Mid$(LineOfText$, N, 1) = Character$ Then LastOfThese = N Next N End Function -- Regards Robin "delmac" wrote: HI all, has anyone got a formula for finding thwe 1st space from the right of a string e.g. These can have variable lengths as they are all addresses Gar 29 Durban Avenue Dalmuir West C I want to find the space between West and C Thanks all (again) -- delmac |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
space from right
I've got an imported file of 12000 address and I only need the 1st line which
ends at the last space from the right. I can then using len and mid to delete the last part of the string if I know where the last space is. Regards -- delmac "Rick Rothstein" wrote: A couple of questions. First off, are you asking for a worksheet formula solution or a VB solution? Second, what is the goal of looking for that last space... in order to find the last "word" in the line? If so, that would be the better question to ask... in other words, tell us what you are ultimately trying to accomplish as there *may* (no guarantee) be a better way to do it than the one you have in mind. -- Rick (MVP - Excel) "delmac" wrote in message ... HI all, has anyone got a formula for finding thwe 1st space from the right of a string e.g. These can have variable lengths as they are all addresses Gar 29 Durban Avenue Dalmuir West C I want to find the space between West and C Thanks all (again) -- delmac |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
space from right
Mike has given you a formula to find the last space which you can use in a
MID function call as you mentioned; however, you might want to consider this alternative which will give you the part of the data you asked for in a shorter function using less function calls... =SUBSTITUTE(A1," "&TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)),"") It is because of alternative methods of accomplishing a goal like this that I asked you "why" you wanted to find the last space. -- Rick (MVP - Excel) "delmac" wrote in message ... I've got an imported file of 12000 address and I only need the 1st line which ends at the last space from the right. I can then using len and mid to delete the last part of the string if I know where the last space is. Regards -- delmac "Rick Rothstein" wrote: A couple of questions. First off, are you asking for a worksheet formula solution or a VB solution? Second, what is the goal of looking for that last space... in order to find the last "word" in the line? If so, that would be the better question to ask... in other words, tell us what you are ultimately trying to accomplish as there *may* (no guarantee) be a better way to do it than the one you have in mind. -- Rick (MVP - Excel) "delmac" wrote in message ... HI all, has anyone got a formula for finding thwe 1st space from the right of a string e.g. These can have variable lengths as they are all addresses Gar 29 Durban Avenue Dalmuir West C I want to find the space between West and C Thanks all (again) -- delmac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Space removal - a first name,space last name | Excel Discussion (Misc queries) | |||
Wildcard for space text space pattern | Excel Programming | |||
Can I automatically add space after ea. row ("Space After" in Word | Excel Discussion (Misc queries) | |||
formula that will go up one space if no value in specified space | Excel Worksheet Functions | |||
Paper Space / Model Space ? | Excel Discussion (Misc queries) |