Conditional string formula to produce sheet value

[d1onys0s]

I am creating a cheat sheet which grabs cell values from another sheet. Currently, I use the MID function to produce a certain string of text within the reports.

However, there is a certain cell which provides a value which is either 28 string long or 32 string. So far I am using this formula:

=IF(LEN([c14.xls]Sheet1!$A$197)>28,[MID([c14.xls]Sheet1!$A$197,8,15)],[MID([c14.xls]Sheet1!$A$197,8,15)])

This function does not currently work. Basically, my goal is to grab a certain string from the cell when the text is 28 and grab a different value when the text string is 32.

Thank you.

 

[d1onys0s]

Is there any way to use the MID function (or something that works like it) within the conditional?

 

[Momentman]

You can use MID in there

Do you mean when the length is equal to 28 or when its greater than 28.

Can the length be anything different from 28 and 32?

Example of how to structure the IF

=IF(len(cell)=28,"use mid",if(len(cell)=32,"Use mid again","show a blank"))

maybe be more specific, then someone can help write the formula directly

 

[d1onys0s]

The cell length is either 28 or 32.

If cell is greater than 28, grab text starting at string 12

If cell is not greater than 28, grab text starting at string 8

So I have:
IF(len(cell)>28,[MID(cell),12,12],[MID(cell),8,12]

This is producing an error, however.

 

[Momentman]

Shouldn't you be using an equal sign instead of the greater than symbol?

=IF(len(cell)=28,[MID(cell),12,12],[MID(cell),8,12]).........whats the exact location of the cell you want to extract from?

is the formula in the same workbook as the cell it is referencing, if it is, i don't see the reason for the square brackets

Whats the cell from which you want to extract your string?

 

[d1onys0s]

Yes, I can use an equal sign. I did not initially because I did not count the exact length of the cell which has 32.

This formula is in a different workbook as the cell we are referencing.

The cell I am after is this: [c14.xls]Sheet1!$A$197

 

[Momentman]

Note that for the formula to work, the referenced workbook should be open (or so i think)

=if(len([c14.xls]Sheet1!$A$197)=28,mid([c14.xls]Sheet1!$A$197,12,12),mid([c14.xls]Sheet1!$A$197,8,12))

 

[d1onys0s]

Outstanding! thanks so much.

Now I am attempting to apply conditional formatting =if(len([c14.xls]Sheet1!$A$197)=28

I went to condition "use a formula to determine which cells to format" and entered the above formula in "Format values where this formula is true"

This conditional formatting did not result in my desired formatting.

I want to apply a colored font if the cell length was equal to 28.

 

[Momentman]

If for example, you want to format cells A1 to A20, just highlight A1 to A20 and in the formula bar for conditional formatting with a formula, simply type

len([c14.xls]Sheet1!$A$197)=28

Without the IF preceding it

 

[d1onys0s]

For some reason this is not working in "conditional formatting with a formula":

="LEN('C:\MYfolder\Project\[c14.xls]Sheet1'!$A$197)=28"