 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2006PLS, Help: Separate a string that was entered with the help of Alt+Enter
Hi, All I have a datafile that was generated by accounting software. My proble is follow: I have a cell there with a string value, it contains tex that looks like it was enetered with the help of Alt+Enter in a cel (looks like several rows in one cell). I need to separate string into several string subvalues and cant understand how to recognize tha nonprintable char between the subvalues. May be some can gives me a advise or other way how to do it... thanks in advanc >Hi, All >I have a datafile that was generated by accounting software. My problem [quoted text clipped - 5 lines] >advise or other way how to do it... >thanks in advance "Regular Expressions" can handle this problem fairly simply. If the length of the strings is <=255, then Longre's free morefunc.xll add-in allows you to use them in worksheet formulas. You can obtain this from http://xcell05.free.fr and can use the functions either as worksheet formulas, or as part of a VBA routine. If the strings are longer, then VBScript (usable from within VBA) allows functionality with the longer strings. But it'd be a bit more complicated to implement. --ron OK, i've downloaded adds-in. Which function I need to use? There is n charcode for separator (I don't know) which is generated by pressin Alt+Enter... Ron Rosenfeld Wrote: > >Hi, All > >I have a datafile that was generated by accounting software. M [quoted text clipped - 24 lines] > > --ro The separator which forces a new line within a cell (equivalent to Alt-Enter) is actually character code 10 - you can type this from the keyboard as ALT-010 on the numeric keypad. Ron seems to be the expert on morefunc, so I'll let him come back on that. Hope this helps. Pete > OK, i've downloaded adds-in. Which function I need to use? There is no > charcode for separator (I don't know) which is generated by pressing [quoted text clipped - 36 lines] > StanUkr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27232 > View this thread: http://www.excelforum.com/showthread.php?threadid=550295 Thank for that, but all I need is to put separatly substrigs from a cel that are separated by Alt+Enter into 1 row to a different columns.... When i tried to use function like this =SUBSTITUTE(C6;CHAR(10);"$") i has no effect. Then i suppose to use "text to a columns" afte separator will be substituted with $ sнymbol... Thank for that, but all I need is to put separatly substrigs from a cel that are separated by Alt+Enter into 1 row to a different columns.... When i tried to use function like this =SUBSTITUTE(C6;CHAR(10);"$") i has no effect. Then i suppose to use "text to a columns" afte separator will be substituted with $ sнymbol... Since I've run into this before, I'll barge in. I'll presume the text you want to separate is in column G and you want to put the separated text into the columns following G. Put this macro in a regular module, then return to the worksheet and run the macro. Sub SeparateText() Dim j As Integer, k As Long Dim ctCR As Integer For k = 2 To Cells(65536, "g").End(xlUp).Row ctCR = 1 With Cells(k, "g") .Offset(, 1) = "" For j = 1 To Len(.Value) If Asc(Mid(.Value, j, 1)) = 10 Then ctCR = ctCR + 1 .Offset(, ctCR) = "" Else .Offset(, ctCR) = .Offset(, ctCR) & Mid(.Value, j, 1) End If Next j End With Next k End Sub > Thank for that, but all I need is to put separatly substrigs from a cell > that are separated by Alt+Enter into 1 row to a different columns.... [quoted text clipped - 7 lines] > StanUkr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27232 > View this thread: http://www.excelforum.com/showthread.php?threadid=550295 Use TTC (Text to Columns) ! Select column of text, then: <Data> <TextToColumns> <Delimited> <Next>, Click in <Other>, Then, hold down the <Alt> key, and type 010 Using the numbers from the Num keypad, *not* the numbers under the function keys. Nothing will be visible in the box, but you should now see your data properly separated in the Data Preview window. Then <Finish>.  SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Thank for that, but all I need is to put separatly substrigs from a cell that are separated by Alt+Enter into 1 row to a different columns.... When i tried to use function like this =SUBSTITUTE(C6;CHAR(10);"$") it has no effect. Then i suppose to use "text to a columns" after separator will be substituted with $ sнymbol.... SignatureStanUkr ------------------------------------------------------------------------ StanUkr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27232 View this thread: http://www.excelforum.com/showthread.php?threadid=550295 Thanks, that was really simple and the most right solution! RagDyeR Wrote: > Use TTC (Text to Columns) ! > [quoted text clipped - 34 lines] > View this thread > http://www.excelforum.com/showthread.php?threadid=55029 Appreciate the feed-back. SignatureRegards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- Thanks, that was really simple and the most right solution! RagDyeR Wrote: > Use TTC (Text to Columns) ! > [quoted text clipped - 38 lines] > View this thread: > http://www.excelforum.com/showthread.php?threadid=550295 SignatureStanUkr ------------------------------------------------------------------------ StanUkr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27232 View this thread: http://www.excelforum.com/showthread.php?threadid=550295 >OK, i've downloaded adds-in. Which function I need to use? There is no >charcode for separator (I don't know) which is generated by pressing >Alt+Enter... The character is a new line, ASCII code 10, and you could use a \n to represent it in a regex. But this may not even be necessary. The functions you would use are the REGEX... functions. How you construct the expression and exactly which function to use will depend on more specifics than you've shared with us to date. --ron >OK, i've downloaded adds-in. Which function I need to use? There is no >charcode for separator (I don't know) which is generated by pressing >Alt+Enter... The character is a new line, ASCII code 10, and you could use a \n to represent it in a regex. But this may not even be necessary. The functions you would use are the REGEX... functions. How you construct the expression and exactly which function to use will depend on more specifics than you've shared with us to date. Expanding on that statement, we need the specifications for what substrings you wish to extract. How are they characterized? By position? By format? etc. For example, if the substring is a date, we would construct a regex to look for a pattern that looks like a valid date. If the substring is a yield and always has a % sign at the end, we would construct a regex to look for a number that ends with a % sign. If the substring is the first word in the third line, we would construct a regex to look for the second instance of a word preceded by a newline character. etc. --ron |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.