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

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

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

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

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


.

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


.

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


.

.

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


.

.

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


.

.

.

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


.

.

.