Find and move text

For the first two examples, assuming they appear in cell A1, the following would work ...

=TRIM(RIGHT(A1,3)) & TRIM(LEFT(A1,LEN(A1)-3))

However, your third example seems to suggest that you want text after the second "x" taken out - is that correct?

Andrew

Sorry that was an error - the data after the second x should also be there.

Thanks

Andrew, I am again sorry as I didn't give a full explanation on the original post. The data will change every-time I use it, as it is copied in from others file. So the formula needs to identify how many letters are at the end and to put them at the front.

OK, still assuming your original value is in A1, try ...

=TRIM(LEFT(RIGHT(A1,MATCH(FALSE,INDEX(ISERROR(VALUE(MID(A1,(LEN(A1)+1)-ROW(INDIRECT("A1:A" & LEN(A1))),1))),0),0)-1) & A1,LEN(A1)))

Andrew I'm getting an error in the formula at the point (LEN(A1)+1) the LEN is highlighted. As I have absolutely no idea what I am looking at I'm stuck

Could you post an example workbook? My formula works for me, so I'll need to see what you're trying to convert.

Andrew

Please find attached a sample file with what I am trying to achieve. I have manually entered sample data in column G what I am trying to get to.

Ah, I take it you're using Excel 2003. It's giving an error because it doesn't allow nesting of formula deeply enough for the method I'm using.

I can write a user-defined function, if you want, but it will depend on macros being enabled in the workbook.

You're correct with your assumption that I'm using 2003. I have macro's enable to bring the data in so I'll use whatever you can provide to get this to work - many thanks.

OK, this function should work fine as a worksheet function. It's not the neatest in the world, but seems to work OK for me ...

Function TextToFront(ByVal sFullString As String) As String

Dim lFindLoop

On Error GoTo ExitFunction

lFindLoop = Len(sFullString)

If lFindLoop > 0 Then
  While lFindLoop > 0 And Not (IsNumeric(Mid(sFullString, lFindLoop, 1)))
    lFindLoop = lFindLoop - 1
  Wend
End If

ExitFunction:

If lFindLoop = 0 Then
  TextToFront = sFullString
Else
  TextToFront = Mid(sFullString, lFindLoop + 1) & Left(sFullString, lFindLoop)
End If

End Function

Andrew - thanks for this - the question is how do I get this function to work?

Open your workbook.

Press Alt-F11 to open the VB editor

From the Insert menu select "Module"

A new module will open and you can paste my code straight in.

On your workbook you can now use =TEXTTOFRONT as a worksheet function, so if the value 123abc is in cell A1 then using =TextToFront(A1) in another cell will return abc123.

Andrew I am still having problems - I have followed your instruction to the letter but I now get a #Name? error within the cell.

Andrew - disregard my last - everything is fine - many many thanks for all you have done