Looking for more efficient formula using FIND and VALUE

[cubs610]

Hey Everyone,

I'm working with a formula and am curious to see if there is a more efficient (read: streamlined) way of doing it.

Columns P and Q were extracted from information in column G. The goal is to look at the values in column G, which are manually input from people and separate them into new cells (columns) at the semicolon. So for example some sample values are: 0.0246;0.024 and 0.019;0.019 and 0.0261;0.0338 (see attached file for details)

The current formula in: COLUMN P =VALUE(MID(G3,1,FIND(";",G3,1)-1))
The current formula in: COLUMN Q =VALUE(MID(G3,FIND(";",G3)+1,10))

Just curious to see what everyone thinks.
Love to hear your thoughts!

Thanks,
Dave

 

[Luke M]

Looks pretty good as-is, but if you really want to trim down the overall length, could do this.

Col P:
=1*LEFT(G3,FIND(";",G3)-1)

Col Q:
=1*MID(G3,FIND(";",G3)+1,10)

 

[Khalid NGO]

Hi Dave,

You can also use the Text to Column Wizard:

• Copy Column G and Paste to Column P
• Select Column P, Go to Data > Text to Columns
• Delimited > Next
• Semicolon > Finish

Or just for fun, try this long but single formula in P3:

=TRIM(MID(SUBSTITUTE($G3,";",REPT(" ",LEN($G3))),(LEN($G3)*(COLUMN(A1)-1))+1,LEN($G3)))+0

Copy down and across to column Q.

Regards,

 

[AVK]

In excel2007 you can also use below formula :
In "P3" =LEFT(G3,FIND(";",G3)-1)
In "Q3" =RIGHT(G3,LEN(G3)-FIND(";",G3))

 

[cubs610]

Looks pretty good as-is, but if you really want to trim down the overall length, could do this.

Col P:
=1*LEFT(G3,FIND(";",G3)-1)

Col Q:
=1*MID(G3,FIND(";",G3)+1,10)

Hey Luke... great stuff! Question- what does the 1* portion of the formula do? I've never seen that before.

Best,
Dave

 

[cubs610]

Hi Dave,

You can also use the Text to Column Wizard:

• Copy Column G and Paste to Column P
• Select Column P, Go to Data > Text to Columns
• Delimited > Next
• Semicolon > Finish

Or just for fun, try this long but single formula in P3:

=TRIM(MID(SUBSTITUTE($G3,";",REPT(" ",LEN($G3))),(LEN($G3)*(COLUMN(A1)-1))+1,LEN($G3)))+0

Copy down and across to column Q.

Regards,

Hey Khalid-- many thanks!!!

 

[cubs610]

In excel2007 you can also use below formula :
In "P3" =LEFT(G3,FIND(";",G3)-1)
In "Q3" =RIGHT(G3,LEN(G3)-FIND(";",G3))

Hey AVK,

Sweet! I like it... many thanks!
-Dave

 

[Khalid NGO]

Question- what does the 1* portion of the formula do?

Hi Dave,

*1 (multiply by 1) and =VALUE() doing the same thing i.e. converting Text result into real numbers, as the MID/LEFT/RIGHT etc are text function and produce result in Text, which can not be used in further calculations.

You can test it by removing *1 in Luke's formula
and removing VALUE() in your formula.


These can be used to convert text numbers into real numbers:
A1*1
A1+0
A1^1
--A1
=VALUE(A1)

Where A1 contains the text numbers.

Hope it helps.

Regards,

 

[Luke M]

Hey Luke... great stuff! Question- what does the 1* portion of the formula do? I've never seen that before.

Best,
Dave

Khalid hit it on the head. Whenever you tell XL to do a mathematical operation, it will automatically try to convert text strings into numbers, if possible. So, it's just a shorter way of writing the VALUE function.