Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

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

Posted on 2011-09-11
Medium Priority
188 Views
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>
03:  MOVE ME FIRST

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>]
==================
<end>
file attached to test variations of strings, character lengths Cut-Plan.xlsx
0
Question by:Berry Metzger
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3

LVL 6

Expert Comment

ID: 36521213
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
0

LVL 50

Expert Comment

ID: 36521220
With data in A1 try this formula

=MID(A1&CHAR(10)&A1,FIND(CHAR(10),A1,FIND(CHAR(10),A1)+1)+1),LEN(A1))

regards, barry
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 36521607
Sorry, there was an additional superfluous parenthesis in that formula - should be like this if referencing C2

=MID(C2&CHAR(10)&C2,FIND(CHAR(10),C2,FIND(CHAR(10),C2)+1)+1,LEN(C2))

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

see attached

regards, barry
27302730.xlsx
0

LVL 50

Expert Comment

ID: 36521615
Note: you also need to widen column E to see text displayed correctly......

barry
0

Author Closing Comment

ID: 36522246
This formula is what I needed.  It is an easier implementation than using a VB macro.
Thanks once again Barry.
Berry
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month9 days, 13 hours left to enroll