Excel 2K - Len & Left 1

[treta]

Hi
Can you help me with this:
I have a cell with this text: 010.70294
How determinate the len just to . (point)
I want to separete in 2 columns 010 and 70294

Thanks
Antonio

 

[Molby]

Use the Text to Columns function under the Data menu with a delimiter of "." This will split any data into two columns where a "." occurs.

 

[KenWright]

In formulas

=LEFT(A1,FIND(".",A1)-1) gets you to the left

=RIGHT(A1,LEN(A1)-FIND(".",A1)) gets you to the right

BUT

Data / Text To Columns / Delimited / Period aselimiter will do the lot for you in one go. Select entire range first though.

Regards
Ken................


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[dyarwood]

Use the instr function to determine the position of the '.' in the left function

 

[treta]

Thanks Ken it works.

Dyarwood I tried to use instr like I used to do with Access but don't work:left(stringtext,Instr(1,stringtext,".")-1)
Whi not? what I'm doing wrong?

Thanks again

 

[dyarwood]

I just used this for some data in A1 and it worked happily on Excel XP. Not sure if there are any issues with Instr and excel 2K.

Left(Range("A1"), InStr(1, Range("A1"), ".") - 1)

 

[treta]

dyarwood thanks but in excel 2K don't work - don't find InStr.(Not full instalation?
I'll look at home with my excel 2K

Thanks again

 

[xlbo]

I think that InStr was only added to XP onwards

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

I think there is a bit of confusion here between VBA (this is the VBA Forum) and Worksheet Functions.

INSTR is the VBA Function
FIND is the Worksheet Function which does the same thing.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[jcrater]

If you just want the length of the left portion, how about:
Len(Int(A1))?

 

[TonyJollans]

jcrater,

In the posted example that will ignore the leading zero - giving 2 instead of 3.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[PHV]

As we are in the VBA forum and you use excel 2k you may consider the Split function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[treta]

Ok thsnks you all to point mi some solutions