Excel Help

oxygene

oxygene

oxygene

Soldato
Joined
20 Aug 2004
Posts
3,115
Location
Bournemouth
I always like to come to you chaps for this kind of help as the other forums never quite have what I need!

Basically, some information is pasted in to a spreadsheet. This information comes in the form of a number and decimal, as below:
12.34
Now I need to strip this down and place the seperate parts in to columns. This I've done with:
=LEFT(I13,SEARCH(".",I13)-1)
=TRIM(RIGHT(I14,LEN(I14)-FIND(".",I14)))
This does the job but for a couple of use cases as below:

12.34 12 34
2.34 2 34
5.02 5 02
1.20 1 2

My major issue at the moment is the last one. This information comes in as 1.2, but I solved that easily enough with adding decimal places to the column.
But the output must be 20, for processing sake, not 2.
The main option I've found online is to convert it to a text string first, but that outputs 02 as it works from the right.
Does anyone have some ideas which would cover all cases? (Bearing in mind I dont want to go with VB for Excel!)

Much appreciated!
 
If the number you want to split is in A1 then instead of pointing to A1 directly use this:

TEXT(A1,"0.00")


EDIT for clarity:

So I would just add that in to your formulas like this:

Code: 
=LEFT([COLOR="Yellow"]TEXT(I13,"0.00")[/COLOR],SEARCH(".",[COLOR="Yellow"]TEXT(I13,"0.00")[/COLOR])-1)
=TRIM(RIGHT([COLOR="Yellow"]TEXT(I14,"0.00")[/COLOR],LEN([COLOR="Yellow"]TEXT(I14,"0.00")[/COLOR])-FIND(".",[COLOR="Yellow"]TEXT(I14,"0.00")[/COLOR])))
 
Last edited:
I'm afraid I don't quite understand what you're trying to do and why you're searching for the decimal points.
If you're after splitting a text value of "1.20 1 2" into "1.20", "1" and "2" then you could instead search for spaces and use left/mid to strip out the substrings. TRIM is useful to remove any excess spaces.
 
wonko said:
I'm afraid I don't quite understand what you're trying to do and why you're searching for the decimal points.
If you're after splitting a text value of "1.20 1 2" into "1.20", "1" and "2" then you could instead search for spaces and use left/mid to strip out the substrings. TRIM is useful to remove any excess spaces.
Click to expand...

My apologies, the table format didnt show well on my post, and I dont have an image here.
The table is :
A1 1.20
A2 1
A3 20

Thats the output I want, but the one I am getting has A3 as 2
 
No problem :) Does this do what you want?

Code: 
A1 "1.20 1 20"
A2 =TRIM(A1)
A3 =SEARCH(" ",TRIM(A2))
A4 =SEARCH(" ",TRIM(A2),A3+1)
A5 =LEFT(A2,A3-1)
A6 =MID(A2,A3+1,A4-A3-1)
A7 =MID(A2,A4+1,LEN(A2)-A4)

Edit: Ah, sorry I think I'm being slow - I think I see what you're after now... my version of your formula would be
A2 =TRIM(A1)
A3 =SEARCH(".",A2)
A4 =LEFT(A2,A3-1)
A5 =MID(A2,A3+1,LEN(A2)-A3)

As long as A1 is text string with the extra zero it should work.
 
Last edited:
Sorry chaps the first post was very unclear! I now have access to picture posting so this is what I'm going for:

27VhiXZ.png


The A column is obviously input of various types.
B column is the numbers before the decimal point.
The C column is numbers after.
But as we can see it doesnt work for 1.20 unless I convert the 1.20 in to text, but its pasted in as 1.2 from its source, and it turns in to 1.20 from autoformatting it as a number.
 
Still a bit unclear what you are trying to do, but for column B surely you could use =INT(A1) and for column C, maybe =A1-INT(A1).

EDIT: Are these representing times? e.g. 1 hour 20 minutes?
 
Last edited:
So does Grrrrr's suggestion, which is to convert the number in column A into a text string with 2 decimal places, not do the job?
 
Sorry just had a chance to test Armageus' method.
To answer everyones questions it is acheivement scores.
For example with 1.2, this is level one, 20% progress. But it has to be split in to a sentence:
20% of level 1.
But with the majorty of the above methods, including the integer one, it results in the 1.20 second part being converted to 2, instead of 20.
 
oxygene said:
Sorry just had a chance to test Armageus' method.
To answer everyones questions it is acheivement scores.
For example with 1.2, this is level one, 20% progress. But it has to be split in to a sentence:
20% of level 1.
But with the majorty of the above methods, including the integer one, it results in the 1.20 second part being converted to 2, instead of 20.
Click to expand...

All sorted!
After multiple complex methods the integer one worked perfectly!
Cant believe it lol. Sometimes we don't see the simple things in front of us.
For those who want to know:
___________________________________________________
A1___1.20___A2___=INT(A1)___A3___=(A1-(INT(A1)))*100
 
oxygene said:
Sorry just had a chance to test Armageus' method.
To answer everyones questions it is acheivement scores.
For example with 1.2, this is level one, 20% progress. But it has to be split in to a sentence:
20% of level 1.
But with the majorty of the above methods, including the integer one, it results in the 1.20 second part being converted to 2, instead of 20.
Click to expand...

Try:
B1 =INT(A1)
C1 =(A1-INT(A1))*100
D1 =C1 & "% of level " & B1


EDIT: posted a second too late - but glad you solved it :)
 
Integers are whole numbers so the computer will drop the zero. There should be a proper way to do this without faffing around with strings to hack it. Have you tried using the currency or the custom category in format cells?
 
Th0nt said:
Integers are whole numbers so the computer will drop the zero. There should be a proper way to do this without faffing around with strings to hack it. Have you tried using the currency or the custom category in format cells?
Click to expand...

This - format the cell(s) so they show two decimal places. Bob's your uncle.
 
You must log in or register to reply here.
Back
Top Bottom