Help with find in Excel ...
 

Looking for a way to make the cells in column B based on the contents
of cells in colum A (text string) show as below please?

Column A Column B
1 1
1.1 1
1.1.1 1
1.1.1.1 1
10 10
10.1 10
10.1.1 10
10.1.1.1 10
100 100
100.1 100
100.1.1 100
100.1.1.1 100

I'd like to have a formula in each cell in column B that will return
the text string up to the first occurence of the decimal point in
column A but if a decimal point is not found then return the value in
column A anyway.

Hope that makes sense!?!?

Many thanks.

Ian Edmont.

Help with find in Excel ...
 

B1: =--(IF(ISNUMBER(FIND(".",A1)),LEFT(A1,FIND(".",A1)-1),A1))

then copy down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ian Edmont" wrote in message
oups.com...
Looking for a way to make the cells in column B based on the contents
of cells in colum A (text string) show as below please?

Column A Column B
1 1
1.1 1
1.1.1 1
1.1.1.1 1
10 10
10.1 10
10.1.1 10
10.1.1.1 10
100 100
100.1 100
100.1.1 100
100.1.1.1 100

I'd like to have a formula in each cell in column B that will return
the text string up to the first occurence of the decimal point in
column A but if a decimal point is not found then return the value in
column A anyway.

Hope that makes sense!?!?

Many thanks.

Ian Edmont.

Help with find in Excel ...
 

In B1 enter

=IF(ISERR(FIND(".",A1)),A1,LEFT(A1,FIND(".",A1)-1))


and copy down.


"Ian Edmont" wrote in message
oups.com...
Looking for a way to make the cells in column B based on the contents
of cells in colum A (text string) show as below please?

Column A Column B
1 1
1.1 1
1.1.1 1
1.1.1.1 1
10 10
10.1 10
10.1.1 10
10.1.1.1 10
100 100
100.1 100
100.1.1 100
100.1.1.1 100

I'd like to have a formula in each cell in column B that will return
the text string up to the first occurence of the decimal point in
column A but if a decimal point is not found then return the value in
column A anyway.

Hope that makes sense!?!?

Many thanks.

Ian Edmont.

Help with find in Excel ...
 

Ian,

Try:

Place in B1 (or first row with data - change "A1" as needed) and copy down
as needed

=IF(ISERROR(LEFT(A1,FIND(".",A1,1)-1)),A1,LEFT(A1,FIND(".",A1,1)-1))


HTH

"Ian Edmont" wrote:

Looking for a way to make the cells in column B based on the contents
of cells in colum A (text string) show as below please?

Column A Column B
1 1
1.1 1
1.1.1 1
1.1.1.1 1
10 10
10.1 10
10.1.1 10
10.1.1.1 10
100 100
100.1 100
100.1.1 100
100.1.1.1 100

I'd like to have a formula in each cell in column B that will return
the text string up to the first occurence of the decimal point in
column A but if a decimal point is not found then return the value in
column A anyway.

Hope that makes sense!?!?

Many thanks.

Ian Edmont.

Help with find in Excel ...
 

Thanks for the replies everyone.

Ian Edmont.


"Ian Edmont" wrote in message
oups.com...
Looking for a way to make the cells in column B based on the contents
of cells in colum A (text string) show as below please?

Column A Column B
1 1
1.1 1
1.1.1 1
1.1.1.1 1
10 10
10.1 10
10.1.1 10
10.1.1.1 10
100 100
100.1 100
100.1.1 100
100.1.1.1 100

I'd like to have a formula in each cell in column B that will return
the text string up to the first occurence of the decimal point in
column A but if a decimal point is not found then return the value in
column A anyway.

Hope that makes sense!?!?

Many thanks.

Ian Edmont.