Extract Text to Left Before First Space and Return to Right Side (End) of Same String

[seattletimebandit]

Good day.

I have columns of analytical data, some have a leading letter(s) as part of the lab result (i.e., U, UJ, E, B, R), some have no letter.

There is a space between the letter and number:

U 0.042

I need to reverse the string:

0.042 U

If there is no letter (e.g., 0.042 or cell is blank), then just return the number or blank cell.

I've found many ways to do this, but it works only if there is a text w/a space. If there is no leading text/space, it chokes and returns an error (#VALUE!).

Thanks!
stb

 

[silentwolf]

Hi,
would this UDF work for you?

Function ChangeText(ByVal strText As String) As String
    Dim intPos As Integer
    Dim strFB As String
    
    intPos = InStr(strText, " ")
    strFB = Left(strText, intPos)
    
    If intPos = 0 Then
        ChangeText = strText
    
    Else
        ChangeText = Mid(strText, intPos + 1) & " " & strFB
    End If
    
End Function

not sure how to do it in a formula but if you paste that code above into a module you could use this function to do hopefully what you are after.

HTH

 

[Fluff]

Somewhat clunky, but
=IF(I21="","",IF(ISNUMBER(FIND(" ",I21)),RIGHT(I21,LEN(I21)-FIND(" ",I21))&" " &LEFT(I21,FIND(" ",I21)),I21))

 

[seattletimebandit]

Thanks Fluff, that worked as intended.

Silentwolf, thanks to you too. I'll give yours a go later. I've never called a function from a module before. Need me some learnin'.

stb

 

[jtakw]

Hi,

How about this simpler formula solution:

Book1
	A	B
1	U 0.042	0.042 U
2	0.042	0.042
3
4	UJ 0.042	0.042 UJ
Sheet639

Cell Formulas
Range	Formula
B1	=MID(A1&" "&A1,FIND(" ",A1&" ")+1,LEN(A1))

 

[Rick Rothstein]

Somewhat clunky, but
=IF(I21="","",IF(ISNUMBER(FIND(" ",I21)),RIGHT(I21,LEN(I21)-FIND(" ",I21))&" " &LEFT(I21,FIND(" ",I21)),I21))

Less clunky (and I think it will work too)...

=MID(I21&" "&I21,FIND(" ",I21&" ")+1,LEN(I21))