EXTRACTING NUMBERS FROM A TEXT CELL

[SSJ]

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 here:

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

 

[gls858]

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 here:

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]

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]

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

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

 

[Pete_UK]

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

 

[Biff]

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

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

Biff
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 here:

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]

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
As long as the "whitespaces" are in fact standard spaces:

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

Biff
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 here:

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]

Biff,

It worked beautifully!

Thanks
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
As long as the "whitespaces" are in fact standard spaces:

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

Biff
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 here:

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]

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
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
As long as the "whitespaces" are in fact standard spaces:

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

Biff
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 here:

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]

Pete,

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

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

 

[Biff]

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
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
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
As long as the "whitespaces" are in fact standard spaces:

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

Biff
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 here:

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]

You're welcome. Thanks for the feedback!

Biff
Biff,

It worked beautifully!

Thanks
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
As long as the "whitespaces" are in fact standard spaces:

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

Biff
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 here:

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