EXTRACTING NUMBERS FROM A TEXT CELL
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 </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: </FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT </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<<SPAN
style="mso-spacerun: yes"
</SPAN813,936.29</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT </DIV
<DIV<FONT face="Arial Narrow"There are two things I need to learn
he</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT </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) and i was able to extract the number, however, i faced
two problems. </FONT</DIV
<DIV<FONT face="Arial Narrow"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. </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 </DIV
<DIV<FONT face="Arial Narrow"2) How can I parse the data once in
Excel, if I want to do that.</FONT</DIV
<DIV<FONT face="Arial Narrow"</FONT </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--
|