Return value of characters in a string using known start & end positions

[suprsnipes]

Hi,

I'm not quite sure if this is possible but using the Find feature I have identified the characters in a string where I would like to return the numbers in between.

For example in A1 & A2 I have;
Start 6 End 8; return character in position 7 in A3
Start 6 End 9; return character in positions 7 & 8 in A3
Start 6 End 8; return character in position 7 in A3

Appreciate your help,
suprsnipes

 

[VoG]

For

Start 6 End 9 return character in positions 7 & 8

=mid(a1,7,2)

 

[T. Valko]

Hi,

I'm not quite sure if this is possible but using the Find feature I have identified the characters in a string where I would like to return the numbers in between.

For example in A1 & A2 I have;
Start 6 End 8; return character in position 7 in A3
Start 6 End 9; return character in positions 7 & 8 in A3
Start 6 End 8; return character in position 7 in A3

Appreciate your help,
suprsnipes

Sounds like you need something like this...

=MID(cell_reference, start_number, number_of_characters)

For example:

A1 = ABCDEFGHIJ

=MID(A1,6,1) = F
=MID(A1,6,2) = FG
=MID(A1,6,3) = FGH

 

[suprsnipes]

I want to fill a formula down around 1000 rows. It may be one character it could be up to 4, so the MID function is not what I am after. Is there any other way?

 

[VoG]

MID is almost certainly what you want but how are you calculating Start and End?

 

[Scott Huish]

I'm not sure why MID would not be what you were after.

Assuming your string is in D1:

=MID(D1,A1+1,A2-A1-1)

 

[T. Valko]

I want to fill a formula down around 1000 rows. It may be one character it could be up to 4, so the MID function is not what I am after. Is there any other way?

Post SEVERAL representative samples of your data and tell us what results you expect.

 

[suprsnipes]

I am using for the start
In A1.

FIND("I",D1)

And End
In B1.

FIND("N",D1)

Sorry I've made a mistake.

The example should be as follows;

For example in A1 & B1 I have;
Start 6 End 8; return character in position 7 in C1
Start 6 End 9; return character in positions 7 & 8 in C1
Start 6 End 8; return character in position 7 in C1

 

[Scott Huish]

OK, If you change my formula to be C1 instead of D1, does that solve your problem?

 

[suprsnipes]

Wow, thanks guys for your help. I used the following from HOTPEPPER.

=MID(D1,A1+1,A2-A1-1)

Didn't know MID function could do that.


​