Hello!

Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below:

@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29

There are two things I need to learn he

1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths.

2) How can I parse the data once in Excel, if I want to do that.

Thanks in advance
SJ

SSJ wrote:
Hello!

Currently I am unable to download information from the accounting system
in a better format. So one line of information come into Excel in one
cell, hence, the example below:

@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1<
813,936.29

There are two things I need to learn he

1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to
extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and
I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the
formula as the numbers are on varoius lengths.

2) How can I parse the data once in Excel, if I want to do that.

Thanks in advance
SJ


If the document coming from the accounting program is in fact a
text file simply go to file open, and navigate to the file, then open.
It should pop up the Text import wizard and allow you to parse the data
either with a delimiter or fixed width. If the data is already in the
spread sheet go to Data Text to columns and this function will allow
you to parse the data.

gls858

Hello gls858

Thanks for the response!

(1) When I try opening the text file in Excel give me the following error
message: "This file is not in a recognizable format." Therefore I am unable
to get it into Excel that way.

(2) I tried your suggestion to parse it in Excel by going into Data Text
to column and then choosing fixed width. I worked, however, partially. You
see the lines are not even, some are long and some are short. It does not
quite work when have lines in thousands.

Thanks
SJ
----


"gls858" wrote in message
...
SSJ wrote:
Hello!
Currently I am unable to download information from the accounting system
in a better format. So one line of information come into Excel in one
cell, hence, the example below: @1 0325@1 OPENING BALANCE :
.....ESTIMATED COST - MATERIAL@1< 813,936.29
There are two things I need to learn he
1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to
extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and
I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the
formula as the numbers are on varoius lengths.
2) How can I parse the data once in Excel, if I want to do that.
Thanks in advance
SJ


If the document coming from the accounting program is in fact a
text file simply go to file open, and navigate to the file, then open.
It should pop up the Text import wizard and allow you to parse the data
either with a delimiter or fixed width. If the data is already in the
spread sheet go to Data Text to columns and this function will allow
you to parse the data.

gls858

SSJ wrote:
Hello gls858

Thanks for the response!

(1) When I try opening the text file in Excel give me the following error
message: "This file is not in a recognizable format." Therefore I am unable
to get it into Excel that way.

(2) I tried your suggestion to parse it in Excel by going into Data Text
to column and then choosing fixed width. I worked, however, partially. You
see the lines are not even, some are long and some are short. It does not
quite work when have lines in thousands.

Thanks
SJ
----


"gls858" wrote in message
...
SSJ wrote:
Hello!
Currently I am unable to download information from the accounting system
in a better format. So one line of information come into Excel in one
cell, hence, the example below: @1 0325@1 OPENING BALANCE :
.....ESTIMATED COST - MATERIAL@1< 813,936.29
There are two things I need to learn he
1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to
extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and
I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the
formula as the numbers are on varoius lengths.
2) How can I parse the data once in Excel, if I want to do that.
Thanks in advance
SJ

If the document coming from the accounting program is in fact a
text file simply go to file open, and navigate to the file, then open.
It should pop up the Text import wizard and allow you to parse the data
either with a delimiter or fixed width. If the data is already in the
spread sheet go to Data Text to columns and this function will allow
you to parse the data.

gls858


Is the suffix .txt? It looks like it may be a delimited file with
the @ sign as the delimiter. You might try using a space as a delimiter.

I see now also that the numbers were brought over as text. I found this
program very helpful in converting "text numbers" to actual numbers. It
has a lot of other helpful functions also. You can find it at:
http://www.asap-utilities.com/ It's a freebie.

gls858

Using Data | Text-to-columns with @ as the delimiter will give you
almost what you need. You might then need to highlight the final column
(containing "1< 813,936.29") and use Data | Text-to columns again
using < as the delimiter.

In answer to your question 1c, =VALUE(RIGHT(A1,11)) would have given
you a numeric answer to enable you to carry out arithmetic on it.

Hope this helps.

Pete

SSJ wrote:
Hello!

Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below:

@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29

There are two things I need to learn he

1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths.

2) How can I parse the data once in Excel, if I want to do that.

Thanks in advance
SJ

------=_NextPart_000_0006_01C6EC75.288A0320
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
X-Google-AttachSize: 2782

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
<HTML<HEAD
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1"
<META content="MSHTML 6.00.2900.2963" name=GENERATOR
<STYLE</STYLE
</HEAD
<BODY bgColor=#ffffff
<DIV<FONT face="Arial Narrow"Hello!</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT&nbsp;</DIV
<DIV<FONT face="Arial Narrow"Currently I am unable to download information
from the accounting system in a better format. So one line of information come
into Excel in one cell, hence, the example below:&nbsp;</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT&nbsp;</DIV
<DIV
<TABLE style="WIDTH: 516pt; BORDER-COLLAPSE: collapse" cellSpacing=0
cellPadding=0 width=688 border=0 x:str
<COLGROUP<FONT face="Arial Narrow"
<COL style="WIDTH: 516pt; mso-width-source: userset; mso-width-alt: 22016"
width=688</FONT
<TBODY
<TR style="HEIGHT: 15.75pt" height=21
<TD
style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 516pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent"
width=688 height=21
<DIV<FONT face="Arial Narrow"@1 0325@1 OPENING BALANCE :
.....ESTIMATED COST - MATERIAL@1&lt;<SPAN
style="mso-spacerun: yes"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</SPAN813,936.29</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT&nbsp;</DIV
<DIV<FONT face="Arial Narrow"There are two things I need to learn
he</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT&nbsp;</DIV
<DIV<FONT face="Arial Narrow"1) How can I extract just the number.
</FONT</DIV
<DIV<FONT face="Arial Narrow"1a) I tried the using the formula:
=RIGHT(A1, 11)&nbsp;and i was able to extract the number, however, i faced
two problems. </FONT</DIV
<DIV<FONT face="Arial Narrow"1c) The 1st&nbsp;problem was that the
extracted number came out as a text and I was unable to do any
mathematical operation on it, such as, addtion.&nbsp;</FONT</DIV
<DIV<FONT face="Arial Narrow"1b) The 2nd problem was that I had to
change the 'num chars' in the formula as the numbers are on varoius
lengths.</FONT</DIV
<DIV&nbsp;</DIV
<DIV<FONT face="Arial Narrow"2) How can&nbsp;I parse the data once in
Excel, if&nbsp;I want to do that.</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT&nbsp;</DIV
<DIV<FONT face="Arial Narrow"Thanks in advance</FONT</DIV
<DIV<FONT
face="Arial Narrow"SJ</FONT</DIV</TD</TR</TBODY</TABLE</DIV</BODY</HTML

------=_NextPart_000_0006_01C6EC75.288A0320--

Pete,

Your suggestion was very helpful in learning about parsing that i did not
know before.
Thanks
SJ

"Pete_UK" wrote in message
oups.com...
Using Data | Text-to-columns with @ as the delimiter will give you
almost what you need. You might then need to highlight the final column
(containing "1< 813,936.29") and use Data | Text-to columns again
using < as the delimiter.

In answer to your question 1c, =VALUE(RIGHT(A1,11)) would have given
you a numeric answer to enable you to carry out arithmetic on it.

Hope this helps.

Pete

SSJ wrote:
Hello!

Currently I am unable to download information from the accounting system
in a better format. So one line of information come into Excel in one
cell, hence, the example below:

@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1<
813,936.29

There are two things I need to learn he

1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to
extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text
and I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the
formula as the numbers are on varoius lengths.

2) How can I parse the data once in Excel, if I want to do that.

Thanks in advance
SJ

------=_NextPart_000_0006_01C6EC75.288A0320
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
X-Google-AttachSize: 2782

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"
<HTML<HEAD
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1"
<META content="MSHTML 6.00.2900.2963" name=GENERATOR
<STYLE</STYLE
</HEAD
<BODY bgColor=#ffffff
<DIV<FONT face="Arial Narrow"Hello!</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT&nbsp;</DIV
<DIV<FONT face="Arial Narrow"Currently I am unable to download
information
from the accounting system in a better format. So one line of information
come
into Excel in one cell, hence, the example below:&nbsp;</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT&nbsp;</DIV
<DIV
<TABLE style="WIDTH: 516pt; BORDER-COLLAPSE: collapse" cellSpacing=0
cellPadding=0 width=688 border=0 x:str
<COLGROUP<FONT face="Arial Narrow"
<COL style="WIDTH: 516pt; mso-width-source: userset; mso-width-alt:
22016"
width=688</FONT
<TBODY
<TR style="HEIGHT: 15.75pt" height=21
<TD
style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT:
#ece9d8; WIDTH: 516pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt;
BACKGROUND-COLOR: transparent"
width=688 height=21
<DIV<FONT face="Arial Narrow"@1 0325@1 OPENING BALANCE :
.....ESTIMATED COST - MATERIAL@1&lt;<SPAN
style="mso-spacerun: yes"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
</SPAN813,936.29</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT&nbsp;</DIV
<DIV<FONT face="Arial Narrow"There are two things I need to learn
he</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT&nbsp;</DIV
<DIV<FONT face="Arial Narrow"1) How can I extract just the number.
</FONT</DIV
<DIV<FONT face="Arial Narrow"1a) I tried the using the formula:
=RIGHT(A1, 11)&nbsp;and i was able to extract the number, however, i
faced
two problems. </FONT</DIV
<DIV<FONT face="Arial Narrow"1c) The 1st&nbsp;problem was that the
extracted number came out as a text and I was unable to do any
mathematical operation on it, such as, addtion.&nbsp;</FONT</DIV
<DIV<FONT face="Arial Narrow"1b) The 2nd problem was that I had to
change the 'num chars' in the formula as the numbers are on varoius
lengths.</FONT</DIV
<DIV&nbsp;</DIV
<DIV<FONT face="Arial Narrow"2) How can&nbsp;I parse the data once
in
Excel, if&nbsp;I want to do that.</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT&nbsp;</DIV
<DIV<FONT face="Arial Narrow"Thanks in advance</FONT</DIV
<DIV<FONT
face="Arial
Narrow"SJ</FONT</DIV</TD</TR</TBODY</TABLE</DIV</BODY</HTML

------=_NextPart_000_0006_01C6EC75.288A0320--

As long as the "whitespaces" are in fact standard spaces:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1

Biff
"SSJ" wrote in message ...
Hello!

Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below:

@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29

There are two things I need to learn he

1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths.

2) How can I parse the data once in Excel, if I want to do that.

Thanks in advance
SJ

Hmmm.....

I just noticed something:

.......MATERIAL@1< 813,936.29

The number is after the "<" character. If this is the same for all entries then it's even easier:

=TRIM(MID(A1,FIND("<",A1)+1,255))*1

Biff
"Biff" wrote in message ...
As long as the "whitespaces" are in fact standard spaces:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1

Biff
"SSJ" wrote in message ...
Hello!

Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below:

@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29

There are two things I need to learn he

1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths.

2) How can I parse the data once in Excel, if I want to do that.

Thanks in advance
SJ

Biff,

It worked beautifully!

Thanks
SJ
"Biff" wrote in message ...
Hmmm.....

I just noticed something:

......MATERIAL@1< 813,936.29

The number is after the "<" character. If this is the same for all entries then it's even easier:

=TRIM(MID(A1,FIND("<",A1)+1,255))*1

Biff
"Biff" wrote in message ...
As long as the "whitespaces" are in fact standard spaces:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1

Biff
"SSJ" wrote in message ...
Hello!

Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below:

@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29

There are two things I need to learn he

1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths.

2) How can I parse the data once in Excel, if I want to do that.

Thanks in advance
SJ

You're welcome. Thanks for the feedback!

Biff
"SSJ" wrote in message ...
Biff,

It worked beautifully!

Thanks
SJ
"Biff" wrote in message ...
Hmmm.....

I just noticed something:

......MATERIAL@1< 813,936.29

The number is after the "<" character. If this is the same for all entries then it's even easier:

=TRIM(MID(A1,FIND("<",A1)+1,255))*1

Biff
"Biff" wrote in message ...
As long as the "whitespaces" are in fact standard spaces:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1

Biff
"SSJ" wrote in message ...
Hello!

Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below:

@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29

There are two things I need to learn he

1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths.

2) How can I parse the data once in Excel, if I want to do that.

Thanks in advance
SJ

Biff,

I was trying to understand the formula. Can you please explain the addition of 1255 & multiplication of 1 in your formula. What is it doing?

Thanks
SJ
"Biff" wrote in message ...
Hmmm.....

I just noticed something:

......MATERIAL@1< 813,936.29

The number is after the "<" character. If this is the same for all entries then it's even easier:

=TRIM(MID(A1,FIND("<",A1)+1,255))*1

Biff
"Biff" wrote in message ...
As long as the "whitespaces" are in fact standard spaces:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1

Biff
"SSJ" wrote in message ...
Hello!

Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below:

@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29

There are two things I need to learn he

1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths.

2) How can I parse the data once in Excel, if I want to do that.

Thanks in advance
SJ

Sure.....

=TRIM(MID(A1,FIND("<",A1)+1,255))*1

The number you want to extract is at the end of the string:

.......MATERIAL@1< 813,936.29

Since the "<" character is a unique character and is the last character before the number all we need to do is find that character and extract everything to the right of that character. FIND("<",A1)+1 finds the position of the "<" character and then adds 1. This tells the MID function that that is the starting point of the string we want to extract. 255 is the number of characters that we want to extract. This is just an arbitrary number that is large enough to ensure that we extract all the remaining text to the right of the starting point. So, the string that has been extracted to this point looks like this:

=TRIM( 813,936.29)*1

We use the TRIM function to strip out any leading or trailing spaces so that now the string looks like this:

813,936.29

The MID function returns TEXT as its result so the number at this point is TEXT. We use the *1 to coerce the TEXT number into a numeric number so we can use it in other calculations.

Biff
"SSJ" wrote in message ...
Biff,

I was trying to understand the formula. Can you please explain the addition of 1255 & multiplication of 1 in your formula. What is it doing?

Thanks
SJ
"Biff" wrote in message ...
Hmmm.....

I just noticed something:

......MATERIAL@1< 813,936.29

The number is after the "<" character. If this is the same for all entries then it's even easier:

=TRIM(MID(A1,FIND("<",A1)+1,255))*1

Biff
"Biff" wrote in message ...
As long as the "whitespaces" are in fact standard spaces:

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255)*1

Biff
"SSJ" wrote in message ...
Hello!

Currently I am unable to download information from the accounting system in a better format. So one line of information come into Excel in one cell, hence, the example below:

@1 0325@1 OPENING BALANCE : .....ESTIMATED COST - MATERIAL@1< 813,936.29

There are two things I need to learn he

1) How can I extract just the number.
1a) I tried the using the formula: =RIGHT(A1, 11) and i was able to extract the number, however, i faced two problems.
1c) The 1st problem was that the extracted number came out as a text and I was unable to do any mathematical operation on it, such as, addtion.
1b) The 2nd problem was that I had to change the 'num chars' in the formula as the numbers are on varoius lengths.

2) How can I parse the data once in Excel, if I want to do that.

Thanks in advance
SJ