if so you can use the following:

Assuming A1 = ABC-1111-XYZ-99999

B1 is the first part and C1 is the second part use:

B1 = left(A1;len(A1)-5)

C1 = right(A1;len(A1)-5)

Solved

Posted on 2011-10-12

Hi,

I have an excel column in below format

ABC-1111-XYZ-99999

Now I want to separte this column into 2 columns. First column will have text upto last "-"

i.e ABC-1111-XYZ-

and 2nd column after last "-"

i.e 99999

I have an excel column in below format

ABC-1111-XYZ-99999

Now I want to separte this column into 2 columns. First column will have text upto last "-"

i.e ABC-1111-XYZ-

and 2nd column after last "-"

i.e 99999

9 Comments

if so you can use the following:

Assuming A1 = ABC-1111-XYZ-99999

B1 is the first part and C1 is the second part use:

B1 = left(A1;len(A1)-5)

C1 = right(A1;len(A1)-5)

=RIGHT(A1,LEN(A1)-lastinde

=LEFT(A1,lastindexof(A1,"-

I have attached an example workbook to see it in action

Michael

```
Function LastIndexOf(testString As String, findChar As String)
Dim i As Long
LastIndexOf = -1
For i = 1 To Len(testString)
If Mid(testString, i, 1) = findChar Then LastIndexOf = i
Next
End Function
```

example.xls
If the string is variable in length, you can use this more generic solution (assumes the original string is in A1):

Left side = LEFT(A1,FIND(CHAR(1),SUBST

Right side = RIGHT(A1,LEN(A1)-FIND(CHAR

The "-" is the character you are looking for, and the 3 is telling it to find the third occurance.

HTH,

Alan.

=IF(lastindexof(A1,"-")>0,

=IF(lastindexof(A1, "-")>0,RIGHT(A1,LEN(A1)-la

To output nothing when the test character is not found

Michael

A1=ABC-1111-XYZ-99999

B1=LEFT(A1,SEARCH("-",A1,S

C1=RIGHT(A1,LEN(A1)-LEN(B1

=LEFT(A2,LOOKUP(2^15,FIND(

then in C2 this formula to get the rest

=SUBSTITUTE(A2,B2,"",1)

regards, barry

1) Add this UDF to a regular VBA module:

```
Function RetrieveSplitItem(Index As Long, Expression As Variant, Optional Delimiter As String = " ", _
Optional Limit As Long = -1, Optional Compare As VbCompareMethod = vbTextCompare, _
Optional FailIfNoDelimiter As Boolean = False) As Variant
Dim arr As Variant
RetrieveSplitItem = CVErr(9)
If Not (InStr(1, Expression, Delimiter) = 0 And FailIfNoDelimiter) Then
arr = Split(Expression, Delimiter, Limit, Compare)
Select Case Index
Case 0
RetrieveSplitItem = arr
Case Is > 0
If (Index - 1) <= UBound(arr) Then
RetrieveSplitItem = arr(Index - 1)
End If
Case Else
If (UBound(arr) + Index + 1) >= 0 Then
RetrieveSplitItem = arr(UBound(arr) + Index + 1)
End If
End Select
End If
End Function
```

2) Assuming your first entry is in A2, then use this in B2 to get everything up to and including the last "-":

=LEFT(A2,LEN(A2)-LEN(C2))

3) Use this in C2 to get what's after the last dash:

=RetrieveSplitItem(-1,A2,"

For that UDF, the Index value controls which item gets returned:

0 returns the full array

>0 returns the nth element, starting at 1

<0 returns the nth to last element

Title | # Comments | Views | Activity |
---|---|---|---|

Microsoft OneNote 2010 Printer Missing | 2 | 24 | |

Posting V12 | 2 | 16 | |

Search multiple lines | 3 | 26 | |

exporting issues in Access | 2 | 10 |

This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**15** Experts available now in Live!