Extract number from two delimiter

[Bill Howland]

Hello,

I have several text of the following format in a column

int a;
char ball[3];
long c1 [300 ];
char d;

I would like to extract the number between two '[' ']' square
brackets and store it in an adjacent column. If a text does not have
the the square brackets, I would like to have number 1 stored in the
adjacent column. There could be also
spaces in between the brackets (row 3 shows an example).

I would appreciate if anybody can help me using a cell formula to
achieve this.

Thanks in advance,
Bill

 

[Peo Sjoblom]

One way

=IF(ISNUMBER(FIND("[",A1)),--MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)
-1),1)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Peo Sjoblom]

An easier way would be to use data>text to columns, use one bracket as the
first delimiter and do not import the column
without numbers, then repeat for the other delimiter, that would give you
numbers and empty cells, then select the range,
press F5 and select special and blanks, then type 1 and ctrl + enter and you
are done

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

One way

=IF(ISNUMBER(FIND("[",A1)),--MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)
-1),1)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Hello,

I have several text of the following format in a column

int a;
char ball[3];
long c1 [300 ];
char d;

I would like to extract the number between two '[' ']' square
brackets and store it in an adjacent column. If a text does not have
the the square brackets, I would like to have number 1 stored in the
adjacent column. There could be also
spaces in between the brackets (row 3 shows an example).

I would appreciate if anybody can help me using a cell formula to
achieve this.

Thanks in advance,
Bill

 

[Bill Howland]

Thanks very much. It worked great!

Regards
Bill

One way

=IF(ISNUMBER(FIND("[",A1)),--MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)
-1),1)

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

Hello,

I have several text of the following format in a column

int a;
char ball[3];
long c1 [300 ];
char d;

I would like to extract the number between two '[' ']' square
brackets and store it in an adjacent column. If a text does not have
the the square brackets, I would like to have number 1 stored in the
adjacent column. There could be also
spaces in between the brackets (row 3 shows an example).

I would appreciate if anybody can help me using a cell formula to
achieve this.

Thanks in advance,
Bill