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
5
181 Views
Last Modified: 2012-05-12
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
Comment
Question by:Berry Metzger
  • 3
5 Comments
 
LVL 6

Expert Comment

by:TinTombStone
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

Open in new window

0
 
LVL 50

Expert Comment

by:barry houdini
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

by:
barry houdini earned 500 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

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

barry
0
 

Author Closing Comment

by:Berry Metzger
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
If Statement 3 19
Remove duplicates using cell values VBA 2 28
Dropbox in Windows Server 2008 4 27
Excl VBA Find last column in disjointed range selection 18 19
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

805 members asked questions and received personalized solutions in the past 7 days.

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

Join & Ask a Question