VBA CODE TO SELECT ALL ITALISIZED WORDS IN A STRING

  • Good day, I need to split text to columns where the itaisized words are taken to the next column.


    Current Column A = Pied Avocet Recurvirostra avosetta

    When split,

    "New" Column A = Pied Avocet

    and

    Column B = Recurvirostra avosetta


    Once done, I can use TRIM to get rid of superfluous spaces.

    Willl the above be possible with some VBA clever code please?

    • Best Answer

    Try the attached. Click the button on sheet 1


    Assumes that your list is in Column A and Row 1 is a header row.


    Since the words in italics are the scientific name there should always be 2 words (or an initial then a word). The common name can be any number of words.


    The code will also remove any superfluous spaces.


    Code assigned to the button.

  • Cats4711

    Selected a post as the best answer.
  • Hi KjBox

    Wow! I am ever so grateful to you for the code, it would have taken me weeks to do it manually as there are almost a thousand row in the sheet. I am compiling a bird ID book for a secondary school, therefore the spli as I have to use the latin name as a lookup reference to translate it into another language.. Thanks SO much.

    Cats4711

    Couldn't find the LIKE button, but marked your reply as THE :thumbup: BEST!

  • Your welcome. The Like button is the smiley face at the bottom right of the post.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox

    I've encountered a small problem with the translated birding list.

    Your code works a charm on "normal" names.

    Some names however, contain a hyphen which "connect" the two words when spoken in Afrikaans (sort of Dutch-ish). This hyphen needs to stay after the split.

    I'm too scared to fiddle with your code however to keep it with the name.

    The attached file illustrates the problem. Could you please assist me with the modified code>

    Cats4711

  • Do you need the colour fill to be included or is that just for clarity of explanation?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Yes please, the fill denotes the level of vulnerability.

    Sorry for only replying now, we have rolling blackouts..... and I think we are in different timezones. I am in SOuth Africa.

  • No problem, I am in Borneo so 7 hours ahead of you.


    Can you explain the logic of why, in your sample, Arend, Gevlekte has a grey fill in the current configuration but no fill in the required result, is it a case of all rows that have an empty cell in Column C have no fill?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • You are 100% correct. If no vulnerability status (Col C), whole row is not filled. The original doc is wrong.

    Does your code say "keep fill" or does it attribute RGB or Hex numbers? If so, this is what we use:

    The grey fill RGB is R 230, G 231, B 232; and Hex #E6E7E8.


    Row 4: Aasvoël, Swart- Torgos tracheliotos (B,B) should also be grey, we have no species on the red list in South Africa (yet). In summary thus, only light grey fill or no fill.


    Thanks SO much for your assistance.

  • Try changing the code to this

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox

    Please accept my apologies for only thanking you now. These rolling black-outs are playing havoc with our internet connectivity.

    Once more, many thanks! My lists are now 100% complete and correct.

    Regards

  • You're welcome

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Try changing the code to this

    Hi, I also need to split text to columns where the itaisized words are taken to the next column and I am glad I found my answer here. I also found the https://casinosanalyzer.com/on…inos/goldenladycasino.com website where I found so many real money gaming sites links.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!