Excel help please

[Snow-Munki]

Hi,

need some help with excel please, have something like below:

computer 4
computer 5
computer 12
pc 8
router 23
computer 34

I want a formula so that it outputs in one cell that computer is 4, 5, 12, 34

Is that possible ?

Thanks

 

[Participant]

=RIGHT(A1,2)

is what I think you want.

 

[Cobertizo]

One way (not massively elegant, but gets the job done):
[where the top computer is A1, and the 4 is B1 etc]
1. in C1, type =if(A1="computer",B1,"")
2. in C2, type =if(A2="computer",C1&" ,"&B2,C1)
3. Fill that down (i.e. so C3 should read =if(A3="computer",C2&" ,"&B3,C2) and so on).
4. At the bottom of your list, the last value in column C should be what you are after. If you want it to look cleaner, hide column C, and (say) in column D reference the bottom cell you've used in column C. You can use extra columns with a similar formula if you want to do the same for, say, pc or router.

Oh, and forgive me if that isn't 100% perfect - I'm on a computer without Excel at the moment!

[EDIT: if your data isn't currently in columns, then you can do what Robbie G suggests (which may go wrong if your numbers are of varying lengths), but I would suggest that you use the Text to Columns feature (in the Data menu, I think), and delimit by space.]

 

[Participant]

[EDIT: if your data isn't currently in columns, then you can do what Robbie G suggests (which may go wrong if your numbers are of varying lengths), but I would suggest that you use the Text to Columns feature (in the Data menu, I think), and delimit by space.]

1. If the data isn't in columns there's no problem as he already has what he desires.

2. If the numbers are of varying lengths, use

=MID(A1,(FIND(" ",A1,1)),10)

Delimiting is ok but cumbersome if the list is constantly changing / being added to.