how can i remove the first 3 caracters from one column ?

[basante]

hello.
can you help me with this ?
how can i remove the first 3 caracters from each cell in one column ?

thanks.

 

[Brianwarnock]

Lookup the Mid function

Brian

 

[chergh]

use:

=RIGHT(A1,LEN(A1) - 3)

and then copy and paste for each cell in the column.

 

[basante]

thanks guys, a beer for each
now i need to check if the numbering is continuing, if not missing any number in that cell.
can you help me with this ?

many thanks.

 

[chergh]

I'm sure what you just said made sense to you but it didn't to me.

Might want to explain a little more.

 

[Atomic Shrimp]

I think the OP is wanting to check for gaps in what should be a continuous numeric sequence.

In Excel, you can do this by adding a column that has something like this in it (assuming your number sequence is in column A):

=A2-A1-1

Drag to propagate that calculated cell all the way down the sheet, then run your eye down it, looking for any non-zero values.

 

[Brianwarnock]

I think we need more than a bit of guesswork to understand what he is on about.

Brian

 

[Atomic Shrimp]

<shrug> OK. But I thought I did understand the question.

 

[Brianwarnock]

OK Mike you have edited your previuos post after I posted. The point I and I think Chergh are making is that we cannot tie in his post 4 with the first post, perhaps they are not connected but then why not just ask " how do I check that a number sequence in a row/column is continous".
His wording was puzzling to me.

Brian

 

[Atomic Shrimp]

OK Mike you have edited your previuos post after I posted.

I didn't notice it had worked out that way, but I only edited to add an example, not to change my stated understanding of his question (as stated in the unedited first line). But sorry for any confusion that may have added.

I think the wording thing might just be that English isn't his first language.

 

[basante]

Guys, thanks for your interest and help, and sorry for my expression. Mike Gurman you think right, english isn't my first language. I wanted to say " how do I check that a number sequence in a row/column is continous" ,and the formula =A2-A1-1 works fine.
Thanks all.

 

[Brianwarnock]

I think the wording thing might just be that English isn't his first language.

My apologies, should have checked his Location since he had been good enough to supply it. Well done to both, Mike for his response and Basante for his English.

Brian

 

[Atomic Shrimp]

Just thought of something else; It's even easier to spot gaps in a numeric sequence if you use something like this

=IF(A2<>A1+1,"<<Gap","")

 

[basante]

Just thought of something else; It's even easier to spot gaps in a numeric sequence if you use something like this

=IF(A2<>A1+1,"<<Gap","")

when i try this forumula, i get #VALUE!

 

[qafself]

probably means that your values are text not numbers

 

[Atomic Shrimp]

Hmmm... It seems to work for me - maybe it needs additional parentheses in some cases?

How about:

=IF(A2<>(A1+1),"<<Gap","")

When I paste it into the top of column B (assuming the continuous number sequence is in column A), I get results like this:

1	
2	
3	<<Gap
5	
6	
7	<<Gap
9	
10	<<Gap

 

[basante]

I tried different parentheses, no result.
the values are numbers not text.

 

[qafself]

Works fine for me without the parenthesis

Have you tried using the formula auditing tool?

 

[Atomic Shrimp]

probably means that your values are text not numbers

Ah. Yes - because of the string slicing exercise above.

Try:

=IF(Val(A2)<>(Val(A1)+1),"<<Gap","")

 

[basante]

Ah. Yes - because of the string slicing exercise above.

Try:

=IF(Val(A2)<>(Val(A1)+1),"<<Gap","")

I tried, now i get #NAME?