formula that finds a second <CR> [Alt+Enter] within string and moves all characters following to start of string

The formula  needed looks for the second of two <CR> (entered using Alt+Enter) and moves all alpha-characters [words] following the 2nd <CR> to the beginning of the string, leaving unchanged the remaining characters in string except for their (now new) position within string:  

Example As Is (shown below, a single string, <CR> rows created using Alt+Enter):

01: 34"x44" <CR>                          
02:  21"x3' <CR>

Example To Be using formula (one string with alpha-character group first :

03:  [no leading <CR>] MOVE ME FIRST <CR>
01:  34"x44" <CR>
02:  21"x3' [no ending <CR>]
file attached to test variations of strings, character lengths Cut-Plan.xlsx
Berry MetzgerLean process improvement consultantAsked:
Who is Participating?
barry houdiniConnect With a Mentor Commented:
Sorry, there was an additional superfluous parenthesis in that formula - should be like this if referencing C2


pasted to E2 and then "wrap" set in alignment options

see attached

regards, barry
This uses a User Defined Function to achieve the desired result.  Means saving as a .xlsm though!
The function could be dropped into and Add-in or the Personal Macro workbook Cut-Plan.xlsm
Function ReArrange(wText As String) As String

    Dim x As Integer
    Dim rText As String
    Dim lText As String
    Dim pos1 As Long
    Dim pos2 As Long

    pos1 = InStr(1, wText, Chr(10))
    pos2 = InStr(pos1 + 1, wText, Chr(10))
    rText = Right(wText, Len(wText) - pos2)
    lText = Left(wText, pos2 - 1)
    ReArrange = rText & Chr(10) & lText

End Function

Open in new window

barry houdiniCommented:
With data in A1 try this formula


regards, barry
barry houdiniCommented:
Note: you also need to widen column E to see text displayed correctly......

Berry MetzgerLean process improvement consultantAuthor Commented:
This formula is what I needed.  It is an easier implementation than using a VB macro.  
Thanks once again Barry.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.