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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
First Blank Cell in a range 7 35
Excel 2016 - Black cell borders 11 28
Easy Excel formula needed 4 27
Excel vba to add signature to email when created 11 41
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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.
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now