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
182 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

765 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