=MID(A1,FIND(" ",A1,1)+1,4)
=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-1)
=FIND(" ",A13,[startnum],[B][instance][/B])
Function MIDDLE(text As Variant, n As Integer, delimiter As String) As String
MIDDLE = Split(text, delimiter)(n - 1)
End Function
=MIDDLE(A1,2," ")
Neat:
Cell ContainsCode:Function MIDDLE(text As Variant, n As Integer, delimiter As String) As String MIDDLE = Split(text, delimiter)(n - 1) End Function
Code:=MIDDLE(A1,2," ")
[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]
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:
Code:=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:
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.Code:=FIND(" ",A13,[startnum],[B][instance][/B])
Maybe I need to learn UDF... Any sugested starting points?
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) |