Selecting Part of Cell in Excel

For the sake of the example, let's assume the cell is A1.

=left(A1,3)

you can also use a find() function within your left() function in case it's not always just 3 letters

=left(A1,find("-",A1,1,)-1)

note that =mid() and =right() also perform similar functions. These are very useful at times, especially when you use them with the "&" joiner and =text() functions

thanks - MID worked just right!

thanks - MID worked just right!

let's say you want column label to appear in row A (will only work from A-Z):

put in cell A1: +char(counta($A1:A1)+64). drag that across row 1 with automatic calc and iterations.

char(65)=A, char(66) = B, char(67)=C, etc

i actually just thought of a way to modify it such that it will work for AA, BJ, ED, etc (columns beyond Z). let me know if you're curious

thought of a really easy way..

=+MID(ADDRESS(ROW(),COLUMN()),2,IF(COLUMN()27,1,2))

the if statement is there so that when the column is less than 27 (columns A-Z), the mid function returns 1 character (so as not to include the $) and if not it returns two characters (because all other column labels require two characters (i.e. AA, BD, IF)

Not a formula, but this may work. Use the text to columns feature under the data drop down. Just chose the - or whatever other character separates the fields (including a space if you want). This will break up the fields and then you can just select whichever part of the data you want.

I have a long list of codes written in the following format: 20Co400PoHPS1.

Does anyone know how I can select the middle number in this text (i.e. 400) given that the number of characters to the left and right of it changes, and the length of the middle number ranges from 1-4 digits.

Thanks for the quick response! The problem is that they're all just one group of text at this point. Is there a way I can automatically insert a space or dash between each group of characters?

I also tried to just used the fixed width function in text to columns but it doesn't quite work because the width of the 1st 4 characters is going to be different.

Say I needed to select part of a cell, and that particular part was a sum, say 63/54, how would you get Excel to work out the sum for you as well?

Hi..

I am looking for a formula which can pick up a row according to the value which I entering in a cell. Please help me.

Thank you,

Jafar

Hi... I'm looking count only one part of a cell with the format i.e. 1x4 - €105 pro nacht (2 Nächte) + €9 KfZ I have many different rows in the same column and I want to count only the "4" after "1x" because the entire example above is all in the same cell. Can you help me please? From what I read of the other comments, there isn't an exact example yet... Thanks!