Reference only part of a cell

  • Thread starter Thread starter edeaston
  • Start date Start date
E

edeaston

Hi,

I have a column of data on one worksheet that contains two sets of numbers,
with the second number in brackets (see below). This data is actually based
on a mixture of underlying formulas for some cells which change month on
month and some that are manually entered.

2 (4)
5 (5)
11 (22)
6 (9)

On another worksheet I want to display just part of this data - the first
numbers (2,5, 11, 6) but I dont know how. I cant use the LEFT function as the
number could be single or double figures. Any ideas?

Also, is it possible to display the second number on the same worksheet
(different column) using your suggestions?

Thanks in advance, let me know if you need further clarification.

Ed
 
Hi,

For the first number
=LEFT(A1,FIND(" ",A1))*1

and the second
=MID(A1,FIND("(",A1)+1,(FIND(")",A1,1))-(FIND("(",A1,1)+1))*1

Drag both down for subsequent rows.

Mike
 
Hi

For the first number
=--(LEFT(A1,FIND("(",A1)-1))
For the second number
=--(SUBSTITUTE(MID(A1,FIND("(",A1)+1,9),")",""))

The double unary minus -- in front of each formula is just to coerce the
values from being text numbers to numeric.
 
you can use text to column option

select the column

go to data | text to columns | delimited | next | check : space | next
| values which are in brackets select that and column data format as
Text | finish |
 
Hi,

I have a column of data on one worksheet that contains two sets of numbers,
with the second number in brackets (see below). This data is actually based
on a mixture of underlying formulas for some cells which change month on
month and some that are manually entered.

2 (4)
5 (5)
11 (22)
6 (9)

On another worksheet I want to display just part of this data - the first
numbers (2,5, 11, 6) but I dont know how. I cant use the LEFT function as the
number could be single or double figures. Any ideas?

Also, is it possible to display the second number on the same worksheet
(different column) using your suggestions?

Thanks in advance, let me know if you need further clarification.

Ed
Click to expand...

First number:

=--LEFT(A1,FIND(" ",A1)-1)

Second number:

=--TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,")",""),"(",REPT(" ",99)),99))

--ron
 
Thank you to everyone who replied - all of the suggestions fit the bill
perfectly!

Cheers

Ed
 
Hi Mike,

I used this formula in my spreadsheet and this is the result I received for
the following data: (How can I change the formula to account for the -
(dash), which by the way is not always in the same place. I'm trying to
eliminate the space and total from this data.

10007 10007 Total
10008 10008 Total
10012 10012 Total
10013 10013 Total
#VALUE! 10014-3 Total
#VALUE! 10018-3 Total
 
This formula will do it. Replace the A1 with your formula. You are taking
the left characters of your string and trimming off the Total part.

=LEFT(A1,LEN(A1)-6)
 
Back
Top