sorting fractions considered by Excel as text

[Harry Covair]

hi the forum,
Let me present a problem to your sagacity.
In a spreadsheet, I display some Exif attributes of my picture files
The exposure time is expressed as a fraction of a second, eg 1/30 sec, 1/100 sec.
After eliminating the "sec" abbreviation, a fractional expression 1/30 or 1/100 remains; she is considered by Excel as text.
I don't know how, in VBA, to sort these values ​​so that 1/30 comes before 1/100.
Do some people have any idea.
Thank you in advance for your answers.

Harry

 

[Debraj]

Hi Harry,

Is Helper Column Acceptable..

then try this..

=SUBSTITUTE(SUBSTITUTE(B2," Sec",""),"1/","")+0

 

[NARAYANK991]

Hi Harry ,

If you select the cell formats as Fraction , Excel sorts data correctly.

Narayan

 

[Harry Covair]

Hi Debraj, Narayan
@Debraj
I adapted your suggestion in VBA; is working fine except the "+0". It forces a mathematical operation on a text value which leads to an error.
See here my code, with myvalue containing the string to analyze; for example "?1/50 sec."

myvalue = objfolder.GetDetailsOf(strFileName, arrSelectAttrib(i) - 1)
myvalue = Mid(myvalue, 2) ' remove the ?
Worksheets(strActSheet).Cells(a, i + 2).Value = WorksheetFunction.Substitute(myvalue, " sec.", "")

As result, the cell contains well 1/50 as text.
@Narayan
I tried several solutions to convert in VBA the "1/50" text value in fraction.
The function "NumberFormat" doen't not apply to non numeric value.
The function format leads to unwanted result: for a 1/50 it gives 18264 ??/??

Worksheets(strActSheet).Cells(a, i + 2).Value = Format(Worksheets(strActSheet).Cells(a, i + 2).Value, "# ??/??")

Should you have any suggestion to convert a non numeric fractionnal value in a numeric fraction?
Thanks in advance
Harry

 

[NARAYANK991]

Hi Harry ,

Sorry , I did not read through your post fully ; I thought you were talking about a manual sort.

If the values are put into the cells after they have been formatted as Fraction , then Excel automatically converts the values to decimal equivalents of the fractions ; thus 1/100 will become 0.01

Are you already having the data entered into the cells as text ?

Narayan

 

[Harry Covair]

Hi Narayan,
Thanks for your answer.
After converting the Exif value with the substitute function, the cell contains a non numeric fraction value, for example "1/50". An in-depth analyze reveals the presence of an hidden (even in the formula bar) "?" character. Sole the len(cell) function with gives 5 as result or the debug.print (?1/50) attests the presence of this "?" character.
If I remove the "?" with

mid(2,myvalue)

the result in the cell becomes "jan-50" for all non numeric fraction less then 1/100.
That the state of the art.
Any help for a VBA "manipluation" to transform a "1/50" non numeric value in a "1/50" fraction will be welcome.

Thanks in advance for your suggestions
Harry

 

[NARAYANK991]

Hi Harry ,

There are two aspects to your problem :

1. Manipulating the string values that you have into true decimal numbers , so that Excel can sort them correctly

2. Displaying the resulting decimal numbers in proper fraction format

Since VBA does not have any built-in functions to do either of the above , you will need to write procedures to do them.

Check out these two links :

1. http://support.microsoft.com/kb/185424

2. http://visualbasic.ittoolbox.com/gr...display-a-number-in-fractional-format-4136142

Narayan

 

[Marc L]

​

Hi,​

​

don't forget the powerful Evaluate method !​

Sub Demo()
    Const S$ = "1/50"
 
    MsgBox Evaluate(S)
End Sub

Bonne journée !​