PLS, Help: Separate a string that was entered with the help of Alt+Enter

S

StanUkr

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
 
R

Ron Rosenfeld

Hi, All
I have a datafile that was generated by accounting software. My problem
is follow: I have a cell there with a string value, it contains text
that looks like it was enetered with the help of Alt+Enter in a cell
(looks like several rows in one cell). I need to separate string into a
several string subvalues and cant understand how to recognize that
nonprintable char between the subvalues. May be some can gives me an
advise or other way how to do it...
thanks in advance
Click to expand...

"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
 
S

StanUkr

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...
 
P

Pete_UK

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
 
S

StanUkr

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...
 
S

StanUkr

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...
 
J

jkend69315

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
 
R

RagDyeR

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

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


message
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....
 
R

Ron Rosenfeld

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...
Click to expand...

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
 
R

Ron Rosenfeld

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...
Click to expand...

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
 
Top