Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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
5
Medium Priority
?
190 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 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

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

581 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