| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Insert single quote symbol
I have a list of plant names that need a single quote inserted at the
appropriate location as the examples show. Weigela Snowflake Weigela €˜Snowflake Wisteria sinensis Caroline Wisteria sinensis €˜Caroline Vinca minor Alba Variegata Vinca minor €˜Alba Variegata Viburnum x rhytidophylloides Willowwood Viburnum x rhytidophylloides €˜Willowwood Viburnum plicatum f. tomentosum Lanarth Viburnum plicatum f. tomentosum €˜Lanarth Veronica austriaca subsp. teucrium Shirley Blue Veronica austriaca subsp. teucrium €˜Shirley Blue Symphoricarpos x doorenbosii Mother of Pearl Symphoricarpos x doorenbosii €˜Mother of Pearl Syringa vulgaris Andenken an Ludwig Späth Syringa vulgaris €˜Andenken an Ludwig Späth Saxifraga umbrosa var. primuloides Elliott's Variety Saxifraga umbrosa var. primuloides 'Elliott's Variety' Much apprieciate any help given |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert single quote symbol
Each item of data is a set of words
The words are separated by a space. In some of your examples, you need the last word in the set to be encapsulated by single quotes. In other examples, you need the last two words to be encapsulated. How can the formula determine which result to produce?? -- Gary''s Student - gsnu200908 "Gotroots" wrote: I have a list of plant names that need a single quote inserted at the appropriate location as the examples show. Weigela Snowflake Weigela €˜Snowflake Wisteria sinensis Caroline Wisteria sinensis €˜Caroline Vinca minor Alba Variegata Vinca minor €˜Alba Variegata Viburnum x rhytidophylloides Willowwood Viburnum x rhytidophylloides €˜Willowwood Viburnum plicatum f. tomentosum Lanarth Viburnum plicatum f. tomentosum €˜Lanarth Veronica austriaca subsp. teucrium Shirley Blue Veronica austriaca subsp. teucrium €˜Shirley Blue Symphoricarpos x doorenbosii Mother of Pearl Symphoricarpos x doorenbosii €˜Mother of Pearl Syringa vulgaris Andenken an Ludwig Späth Syringa vulgaris €˜Andenken an Ludwig Späth Saxifraga umbrosa var. primuloides Elliott's Variety Saxifraga umbrosa var. primuloides 'Elliott's Variety' Much apprieciate any help given |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert single quote symbol
Are you saying there is no formula however complex will determine this?
"Gary''s Student" wrote: Each item of data is a set of words The words are separated by a space. In some of your examples, you need the last word in the set to be encapsulated by single quotes. In other examples, you need the last two words to be encapsulated. How can the formula determine which result to produce?? -- Gary''s Student - gsnu200908 "Gotroots" wrote: I have a list of plant names that need a single quote inserted at the appropriate location as the examples show. Weigela Snowflake Weigela €˜Snowflake Wisteria sinensis Caroline Wisteria sinensis €˜Caroline Vinca minor Alba Variegata Vinca minor €˜Alba Variegata Viburnum x rhytidophylloides Willowwood Viburnum x rhytidophylloides €˜Willowwood Viburnum plicatum f. tomentosum Lanarth Viburnum plicatum f. tomentosum €˜Lanarth Veronica austriaca subsp. teucrium Shirley Blue Veronica austriaca subsp. teucrium €˜Shirley Blue Symphoricarpos x doorenbosii Mother of Pearl Symphoricarpos x doorenbosii €˜Mother of Pearl Syringa vulgaris Andenken an Ludwig Späth Syringa vulgaris €˜Andenken an Ludwig Späth Saxifraga umbrosa var. primuloides Elliott's Variety Saxifraga umbrosa var. primuloides 'Elliott's Variety' Much apprieciate any help given |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert single quote symbol
Describe how a computer program would know why this is correct:
Symphoricarpos x doorenbosii 'Mother of Pearl' instead of: Symphoricarpos x doorenbosii Mother 'of Pearl' or this: Symphoricarpos x doorenbosii Mother of 'Pearl' Gotroots wrote: Are you saying there is no formula however complex will determine this? "Gary''s Student" wrote: Each item of data is a set of words The words are separated by a space. In some of your examples, you need the last word in the set to be encapsulated by single quotes. In other examples, you need the last two words to be encapsulated. How can the formula determine which result to produce?? -- Gary''s Student - gsnu200908 "Gotroots" wrote: I have a list of plant names that need a single quote inserted at the appropriate location as the examples show. Weigela Snowflake Weigela €˜Snowflake Wisteria sinensis Caroline Wisteria sinensis €˜Caroline Vinca minor Alba Variegata Vinca minor €˜Alba Variegata Viburnum x rhytidophylloides Willowwood Viburnum x rhytidophylloides €˜Willowwood Viburnum plicatum f. tomentosum Lanarth Viburnum plicatum f. tomentosum €˜Lanarth Veronica austriaca subsp. teucrium Shirley Blue Veronica austriaca subsp. teucrium €˜Shirley Blue Symphoricarpos x doorenbosii Mother of Pearl Symphoricarpos x doorenbosii €˜Mother of Pearl Syringa vulgaris Andenken an Ludwig Späth Syringa vulgaris €˜Andenken an Ludwig Späth Saxifraga umbrosa var. primuloides Elliott's Variety Saxifraga umbrosa var. primuloides 'Elliott's Variety' Much apprieciate any help given |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert single quote symbol
Ok, point taken.
How about then first separating the text that needs to be enclosed in single quotes. Apart from the initial capitalization of a plant name where a capitalization begins would be the point where the separation would begin. Here is an example Symphoricarpos x doorenbosii Mother of Pearl Mother of Pearl Once separated Mother of Pearl (the value) would perhaps then be more easily enclosed in single quotes. "Glenn" wrote: Describe how a computer program would know why this is correct: Symphoricarpos x doorenbosii 'Mother of Pearl' instead of: Symphoricarpos x doorenbosii Mother 'of Pearl' or this: Symphoricarpos x doorenbosii Mother of 'Pearl' Gotroots wrote: Are you saying there is no formula however complex will determine this? "Gary''s Student" wrote: Each item of data is a set of words The words are separated by a space. In some of your examples, you need the last word in the set to be encapsulated by single quotes. In other examples, you need the last two words to be encapsulated. How can the formula determine which result to produce?? -- Gary''s Student - gsnu200908 "Gotroots" wrote: I have a list of plant names that need a single quote inserted at the appropriate location as the examples show. Weigela Snowflake Weigela €˜Snowflake Wisteria sinensis Caroline Wisteria sinensis €˜Caroline Vinca minor Alba Variegata Vinca minor €˜Alba Variegata Viburnum x rhytidophylloides Willowwood Viburnum x rhytidophylloides €˜Willowwood Viburnum plicatum f. tomentosum Lanarth Viburnum plicatum f. tomentosum €˜Lanarth Veronica austriaca subsp. teucrium Shirley Blue Veronica austriaca subsp. teucrium €˜Shirley Blue Symphoricarpos x doorenbosii Mother of Pearl Symphoricarpos x doorenbosii €˜Mother of Pearl Syringa vulgaris Andenken an Ludwig Späth Syringa vulgaris €˜Andenken an Ludwig Späth Saxifraga umbrosa var. primuloides Elliott's Variety Saxifraga umbrosa var. primuloides 'Elliott's Variety' Much apprieciate any help given . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert single quote symbol
That helps. Try this array formula (commit with CTRL+SHIFT+ENTER):
=LEFT(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0))&"'"& MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0)+1,LEN(A1))&"'" Probably not optimized very well, but it seems to work. Gotroots wrote: Ok, point taken. How about then first separating the text that needs to be enclosed in single quotes. Apart from the initial capitalization of a plant name where a capitalization begins would be the point where the separation would begin. Here is an example Symphoricarpos x doorenbosii Mother of Pearl Mother of Pearl Once separated Mother of Pearl (the value) would perhaps then be more easily enclosed in single quotes. "Glenn" wrote: Describe how a computer program would know why this is correct: Symphoricarpos x doorenbosii 'Mother of Pearl' instead of: Symphoricarpos x doorenbosii Mother 'of Pearl' or this: Symphoricarpos x doorenbosii Mother of 'Pearl' Gotroots wrote: Are you saying there is no formula however complex will determine this? "Gary''s Student" wrote: Each item of data is a set of words The words are separated by a space. In some of your examples, you need the last word in the set to be encapsulated by single quotes. In other examples, you need the last two words to be encapsulated. How can the formula determine which result to produce?? -- Gary''s Student - gsnu200908 "Gotroots" wrote: I have a list of plant names that need a single quote inserted at the appropriate location as the examples show. Weigela Snowflake Weigela €˜Snowflake Wisteria sinensis Caroline Wisteria sinensis €˜Caroline Vinca minor Alba Variegata Vinca minor €˜Alba Variegata Viburnum x rhytidophylloides Willowwood Viburnum x rhytidophylloides €˜Willowwood Viburnum plicatum f. tomentosum Lanarth Viburnum plicatum f. tomentosum €˜Lanarth Veronica austriaca subsp. teucrium Shirley Blue Veronica austriaca subsp. teucrium €˜Shirley Blue Symphoricarpos x doorenbosii Mother of Pearl Symphoricarpos x doorenbosii €˜Mother of Pearl Syringa vulgaris Andenken an Ludwig Späth Syringa vulgaris €˜Andenken an Ludwig Späth Saxifraga umbrosa var. primuloides Elliott's Variety Saxifraga umbrosa var. primuloides 'Elliott's Variety' Much apprieciate any help given . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert single quote symbol
A cleverly constructed formula if i may say so.
It works beautifully, except a little tweeking is now called for. Plants have there own naming convention hence the varied examples shown earlier. The formula will return N/A when faced with a value such as Arundo donax var. versicolor The above example does not have any capitalization (apart from the first letter) in its name. I need the formula to return these values also even though there is no single quotes to insert into the name. "Glenn" wrote: That helps. Try this array formula (commit with CTRL+SHIFT+ENTER): =LEFT(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0))&"'"& MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0)+1,LEN(A1))&"'" Probably not optimized very well, but it seems to work. Gotroots wrote: Ok, point taken. How about then first separating the text that needs to be enclosed in single quotes. Apart from the initial capitalization of a plant name where a capitalization begins would be the point where the separation would begin. Here is an example Symphoricarpos x doorenbosii Mother of Pearl Mother of Pearl Once separated Mother of Pearl (the value) would perhaps then be more easily enclosed in single quotes. "Glenn" wrote: Describe how a computer program would know why this is correct: Symphoricarpos x doorenbosii 'Mother of Pearl' instead of: Symphoricarpos x doorenbosii Mother 'of Pearl' or this: Symphoricarpos x doorenbosii Mother of 'Pearl' Gotroots wrote: Are you saying there is no formula however complex will determine this? "Gary''s Student" wrote: Each item of data is a set of words The words are separated by a space. In some of your examples, you need the last word in the set to be encapsulated by single quotes. In other examples, you need the last two words to be encapsulated. How can the formula determine which result to produce?? -- Gary''s Student - gsnu200908 "Gotroots" wrote: I have a list of plant names that need a single quote inserted at the appropriate location as the examples show. Weigela Snowflake Weigela €˜Snowflake Wisteria sinensis Caroline Wisteria sinensis €˜Caroline Vinca minor Alba Variegata Vinca minor €˜Alba Variegata Viburnum x rhytidophylloides Willowwood Viburnum x rhytidophylloides €˜Willowwood Viburnum plicatum f. tomentosum Lanarth Viburnum plicatum f. tomentosum €˜Lanarth Veronica austriaca subsp. teucrium Shirley Blue Veronica austriaca subsp. teucrium €˜Shirley Blue Symphoricarpos x doorenbosii Mother of Pearl Symphoricarpos x doorenbosii €˜Mother of Pearl Syringa vulgaris Andenken an Ludwig Späth Syringa vulgaris €˜Andenken an Ludwig Späth Saxifraga umbrosa var. primuloides Elliott's Variety Saxifraga umbrosa var. primuloides 'Elliott's Variety' Much apprieciate any help given . . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert single quote symbol
Brute force:
=IF(ISNA(MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0)),A1, LEFT(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0))&"'"& MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0)+1,LEN(A1))&"'" ) Gotroots wrote: A cleverly constructed formula if i may say so. It works beautifully, except a little tweeking is now called for. Plants have there own naming convention hence the varied examples shown earlier. The formula will return N/A when faced with a value such as Arundo donax var. versicolor The above example does not have any capitalization (apart from the first letter) in its name. I need the formula to return these values also even though there is no single quotes to insert into the name. "Glenn" wrote: That helps. Try this array formula (commit with CTRL+SHIFT+ENTER): =LEFT(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0))&"'"& MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0)+1,LEN(A1))&"'" Probably not optimized very well, but it seems to work. Gotroots wrote: Ok, point taken. How about then first separating the text that needs to be enclosed in single quotes. Apart from the initial capitalization of a plant name where a capitalization begins would be the point where the separation would begin. Here is an example Symphoricarpos x doorenbosii Mother of Pearl Mother of Pearl Once separated Mother of Pearl (the value) would perhaps then be more easily enclosed in single quotes. "Glenn" wrote: Describe how a computer program would know why this is correct: Symphoricarpos x doorenbosii 'Mother of Pearl' instead of: Symphoricarpos x doorenbosii Mother 'of Pearl' or this: Symphoricarpos x doorenbosii Mother of 'Pearl' Gotroots wrote: Are you saying there is no formula however complex will determine this? "Gary''s Student" wrote: Each item of data is a set of words The words are separated by a space. In some of your examples, you need the last word in the set to be encapsulated by single quotes. In other examples, you need the last two words to be encapsulated. How can the formula determine which result to produce?? -- Gary''s Student - gsnu200908 "Gotroots" wrote: I have a list of plant names that need a single quote inserted at the appropriate location as the examples show. Weigela Snowflake Weigela €˜Snowflake Wisteria sinensis Caroline Wisteria sinensis €˜Caroline Vinca minor Alba Variegata Vinca minor €˜Alba Variegata Viburnum x rhytidophylloides Willowwood Viburnum x rhytidophylloides €˜Willowwood Viburnum plicatum f. tomentosum Lanarth Viburnum plicatum f. tomentosum €˜Lanarth Veronica austriaca subsp. teucrium Shirley Blue Veronica austriaca subsp. teucrium €˜Shirley Blue Symphoricarpos x doorenbosii Mother of Pearl Symphoricarpos x doorenbosii €˜Mother of Pearl Syringa vulgaris Andenken an Ludwig Späth Syringa vulgaris €˜Andenken an Ludwig Späth Saxifraga umbrosa var. primuloides Elliott's Variety Saxifraga umbrosa var. primuloides 'Elliott's Variety' Much apprieciate any help given . . |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert single quote symbol
Not quite there yet.
Although the formula returns non single quote values it now returns N/A for any values that contain single quotes. I need both non and single quote values to be returned. "Glenn" wrote: Brute force: =IF(ISNA(MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0)),A1, LEFT(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0))&"'"& MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0)+1,LEN(A1))&"'" ) Gotroots wrote: A cleverly constructed formula if i may say so. It works beautifully, except a little tweeking is now called for. Plants have there own naming convention hence the varied examples shown earlier. The formula will return N/A when faced with a value such as Arundo donax var. versicolor The above example does not have any capitalization (apart from the first letter) in its name. I need the formula to return these values also even though there is no single quotes to insert into the name. "Glenn" wrote: That helps. Try this array formula (commit with CTRL+SHIFT+ENTER): =LEFT(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0))&"'"& MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0)+1,LEN(A1))&"'" Probably not optimized very well, but it seems to work. Gotroots wrote: Ok, point taken. How about then first separating the text that needs to be enclosed in single quotes. Apart from the initial capitalization of a plant name where a capitalization begins would be the point where the separation would begin. Here is an example Symphoricarpos x doorenbosii Mother of Pearl Mother of Pearl Once separated Mother of Pearl (the value) would perhaps then be more easily enclosed in single quotes. "Glenn" wrote: Describe how a computer program would know why this is correct: Symphoricarpos x doorenbosii 'Mother of Pearl' instead of: Symphoricarpos x doorenbosii Mother 'of Pearl' or this: Symphoricarpos x doorenbosii Mother of 'Pearl' Gotroots wrote: Are you saying there is no formula however complex will determine this? "Gary''s Student" wrote: Each item of data is a set of words The words are separated by a space. In some of your examples, you need the last word in the set to be encapsulated by single quotes. In other examples, you need the last two words to be encapsulated. How can the formula determine which result to produce?? -- Gary''s Student - gsnu200908 "Gotroots" wrote: I have a list of plant names that need a single quote inserted at the appropriate location as the examples show. Weigela Snowflake Weigela €˜Snowflake Wisteria sinensis Caroline Wisteria sinensis €˜Caroline Vinca minor Alba Variegata Vinca minor €˜Alba Variegata Viburnum x rhytidophylloides Willowwood Viburnum x rhytidophylloides €˜Willowwood Viburnum plicatum f. tomentosum Lanarth Viburnum plicatum f. tomentosum €˜Lanarth Veronica austriaca subsp. teucrium Shirley Blue Veronica austriaca subsp. teucrium €˜Shirley Blue Symphoricarpos x doorenbosii Mother of Pearl Symphoricarpos x doorenbosii €˜Mother of Pearl Syringa vulgaris Andenken an Ludwig Späth Syringa vulgaris €˜Andenken an Ludwig Späth Saxifraga umbrosa var. primuloides Elliott's Variety Saxifraga umbrosa var. primuloides 'Elliott's Variety' Much apprieciate any help given . . . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Insert single quote symbol
I'm not seeing the problem. Give me an example that returns N/A.
gootroots wrote: Not quite there yet. Although the formula returns non single quote values it now returns N/A for any values that contain single quotes. I need both non and single quote values to be returned. "Glenn" wrote: Brute force: =IF(ISNA(MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0)),A1, LEFT(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0))&"'"& MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0)+1,LEN(A1))&"'" ) Gotroots wrote: A cleverly constructed formula if i may say so. It works beautifully, except a little tweeking is now called for. Plants have there own naming convention hence the varied examples shown earlier. The formula will return N/A when faced with a value such as Arundo donax var. versicolor The above example does not have any capitalization (apart from the first letter) in its name. I need the formula to return these values also even though there is no single quotes to insert into the name. "Glenn" wrote: That helps. Try this array formula (commit with CTRL+SHIFT+ENTER): =LEFT(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0))&"'"& MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))64)* (CODE(MID(A1,ROW($2:$99),1))<91),0)+1,LEN(A1))&"'" Probably not optimized very well, but it seems to work. Gotroots wrote: Ok, point taken. How about then first separating the text that needs to be enclosed in single quotes. Apart from the initial capitalization of a plant name where a capitalization begins would be the point where the separation would begin. Here is an example Symphoricarpos x doorenbosii Mother of Pearl Mother of Pearl Once separated Mother of Pearl (the value) would perhaps then be more easily enclosed in single quotes. "Glenn" wrote: Describe how a computer program would know why this is correct: Symphoricarpos x doorenbosii 'Mother of Pearl' instead of: Symphoricarpos x doorenbosii Mother 'of Pearl' or this: Symphoricarpos x doorenbosii Mother of 'Pearl' Gotroots wrote: Are you saying there is no formula however complex will determine this? "Gary''s Student" wrote: Each item of data is a set of words The words are separated by a space. In some of your examples, you need the last word in the set to be encapsulated by single quotes. In other examples, you need the last two words to be encapsulated. How can the formula determine which result to produce?? -- Gary''s Student - gsnu200908 "Gotroots" wrote: I have a list of plant names that need a single quote inserted at the appropriate location as the examples show. Weigela Snowflake Weigela €˜Snowflake Wisteria sinensis Caroline Wisteria sinensis €˜Caroline Vinca minor Alba Variegata Vinca minor €˜Alba Variegata Viburnum x rhytidophylloides Willowwood Viburnum x rhytidophylloides €˜Willowwood Viburnum plicatum f. tomentosum Lanarth Viburnum plicatum f. tomentosum €˜Lanarth Veronica austriaca subsp. teucrium Shirley Blue Veronica austriaca subsp. teucrium €˜Shirley Blue Symphoricarpos x doorenbosii Mother of Pearl Symphoricarpos x doorenbosii €˜Mother of Pearl Syringa vulgaris Andenken an Ludwig Späth Syringa vulgaris €˜Andenken an Ludwig Späth Saxifraga umbrosa var. primuloides Elliott's Variety Saxifraga umbrosa var. primuloides 'Elliott's Variety' Much apprieciate any help given . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I insert a single quote on every cell in a column to make . | Excel Discussion (Misc queries) | |||
| vlookup and using single quote | Excel Worksheet Functions | |||
| using a single quote ' in SEARCH function | Excel Worksheet Functions | |||
| Remove single quote | Excel Worksheet Functions | |||
| I need a symbol but "symbol" in the Insert menu is grayed-out. | Excel Discussion (Misc queries) | |||
Linear Mode
