how to find character's position in a cell from right to left?

[Lester07]

How can I look up for text/character's position in a cell from right to left using various text functions?

 

[nadernumair]

Easiest way I found is this. If I have a main column A that has "First_Name Last_Name" separated by a space " " and I want to get the position where the last name starts from the right (i.e. position of the first character after the space) is:
=LEN(A1) - FIND(" ",A1)

If I want to extract the First_Name from the main column, I use:
=LEFT(A1,FIND(" ",A1)-1)

If I want to extract the Last_Name from the main column, I use:
=RIGHT(A1,LEN(A1) - FIND(" ",A1))

 

[Sandcastor]

Here's a twist on this request, hope I can find some help here.
I have a list of items in Column A in this format:

121518 1000 A55
60214 16150 C30
072617 10850 C5

These numbers indicate Lot#, [Space], Product ID, [Space], Container ID. All three things are variable sized.
I need to extract the Product ID, which is the only thing between the spaces, in to column B.
I know where to start a MID function with:

=MID(A1,FIND(" ",A1,1)+1,4)

This nets me a 4-digit number starting at the right spot, easy enough, but I cannot find out what to use to indicate a variable length for the MID string.
FIND just finds the first space again

Any suggestions?

 

[jtakw]

Hi,

This will work:

Book1
	A	B	C
1			Product ID
2	121518 1000 A55		1000
3	60214 16150 C30		16150
4	072617 10850 C5		10850
Sheet169

Cell Formulas
Range	Formula
C2	=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),100,100))

Formula copied down.

 

[Sandcastor]

Dirty, But highly effective. I like it.

So If I understand: the formula winnows out the middle term in between some spaces by
1) replacing each space with 100 spaces
2) Grabbing a selection from the middle of that, starting at character 100 and ending 100 further along, then
3) Trimming the spaces away.

Thanks! I'll definitely store that with my snippets.

 

[jtakw]

You're welcome.

Yes, that's exactly what the formula is doing. (not sure what "dirty" means in this context)

 

[Sandcastor]

By dirty, I meant that it is less precise, yet simple and effective.
To contrast, this other solution I figured out is messy: More precise, but much less simple. Example:

=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-1)

Clean would be some hypothetical where is would be easier to dynamically set an endpoint for the MID function, say if the FIND function had another argument to indicate which instance of the string to find:

 =FIND(" ",A13,[startnum],[B][instance][/B])

This would allow you to find the second instance of a "space" in the field, alowing you to pinpoint the end of a MID function.

Maybe I need to learn UDF... Any sugested starting points?

 

[Sandcastor]

Neat:

Function MIDDLE(text As Variant, n As Integer, delimiter As String) As String
    MIDDLE = Split(text, delimiter)(n - 1)
End Function

Cell Contains

 =MIDDLE(A1,2," ")

 

[Rick Rothstein]

Neat:

Function MIDDLE(text As Variant, n As Integer, delimiter As String) As String
    MIDDLE = Split(text, delimiter)(n - 1)
End Function

Cell Contains

 =MIDDLE(A1,2," ")

I thought you wanted the count of characters to the specified delimiter by counting from the right to the left? Your could does not return that a count. The following UDF does...

[table="width: 500"]
[tr]
	[td]Function CountFromRight(S As String, Delim As String, Optional InstanceFromRight As Long = 1) As Long
  Dim DelimCount As Long, Parts() As String
  If InStr(1, S, Delim, vbTextCompare) Then
    DelimCount = (Len(S) - Len(Replace(S, Delim, "", , , vbTextCompare))) / Len(Delim)
    If InstanceFromRight <= DelimCount Then
      Parts = Split(S, Delim, 2 + DelimCount - InstanceFromRight, vbTextCompare)
      CountFromRight = Len(Parts(UBound(Parts))) + 1
    End If
  End If
End Function[/td]
[/tr]
[/table]

Note that this UDF returns 0 if the text does not contain the delimiter or if you specify an instance for the delimiter which does not exist (for example, asking for the count from the right to the second "m" in the word "compost").

 

[Sandcastor]

Hey Rick! It's been a while.

The original poster was looking for a right-to-left solution. I Necro'd the post since it was a couple years old, and on the right track for what I needed. my issue was finding the second delimiter so I knew where to end the MID. I'll take a deeper look at your function later! Always seeking to learn!

 

[jtakw]

By dirty, I meant that it is less precise, yet simple and effective.
To contrast, this other solution I figured out is messy: More precise, but much less simple. Example:

=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-1)

Clean would be some hypothetical where is would be easier to dynamically set an endpoint for the MID function, say if the FIND function had another argument to indicate which instance of the string to find:

 =FIND(" ",A13,[startnum],[B][instance][/B])

This would allow you to find the second instance of a "space" in the field, alowing you to pinpoint the end of a MID function.

Maybe I need to learn UDF... Any sugested starting points?

Ok, a couple of "Clean" (hopefully) formulas for you to consider.

Book1
	A	B	C	D	E
1			Product ID
2	121518 1000 A55		1000	1000	1000
3	60214 16150 C30		16150	16150	16150
4	072617 10850 C5		10850	10850	10850
Sheet169

Cell Formulas
Range	Formula
C2	=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),100,100))
D2	=LEFT(MID(A2,FIND(" ",A2)+1,255),FIND(" ",MID(A2,FIND(" ",A2)+1,255))-1)
E2	=MID(LEFT(A2,FIND("^",SUBSTITUTE(A2," ","^",2))-1),FIND(" ",A2)+1,255)