Split Each Word Into Separate Variable

Questions & Suggestions
, ,
1

Hey all, I am trying to take a row of data that I've pulled from excel, and assign each value to a separate variable. I used to have a Regex formula to accomplish this, but I recently had to reinstall my computers OS and lost a bunch of data due to a backup corruption.

Here is the data:

PEA001	New York, NY	BC	AT&T Spectrum Frontiers LLC	2	$515,167,123	$1,030,334,246	40

And I'm trying to get it to result in this:
Var 1: PEA001
Var 2: New York, NY
Var 3: BC
etc....

Thanks!

2

Hi,

I think the plugins here will help you:

The SPLIT plugin is what you needed.
BTW, @ComplexPoint, I think it'll be good if you could add this sample macro link to the plugins post. so that users may easily find the sample post from the Plugin posts.

3

Unfortunately that isn't gonna do it for me because I need to be able to access each word as a separate variable. I remember @peternlewis posted in a thread a great regex that split up each word into a separate variable using regex and the search and replace tool

4

Hi @DrSabs,

This does exactly what you want.
First you split it with Tab, then you retrieve each separated items with:

Obtaining individual items in the array by a 1-based index , and retrieving the length of the array from index 0 .

5

Oh ok cool! how would I call each variable then? Sorry I'm kinda a noob with array's and such

6

Yes. You can get use some simple actions to achieve your goal, but I thought the plugins are neat. So I thought it might be good to introduce that for you.

Here is an example to use a simple Regex Search and Replace, to replace the Tab with a unique string that you can use as a delimiter to get each items. Here I set it as ~~delimiter~~. You can use any string that will not appear in your data.

RegEx.kmmacros (3.6 KB)

image

Result:

image
image700×300 10.6 KB

7

How do I access the variables created within the split plugin though? I like the idea, but I can't seem to get the variables extracted

8

Nvm, figured it out. Thank you!

1 Like
9

Well, you have to first define and tell us how each "word" is separated.
The example you gave of "New York, NY" clearly contains multiple "words" as commonly defined, and as defined by normal RegEx rules.

Once we know the delimited between fields (what you are calling words) it is fairly easy.
If you get this by a copy from cells in the same row in Excel, then the fields will be separated by TABs.

Here is a simple solution using TABs. It is just an example written in response to your request. You will need to use as an example and/or change to meet your workflow automation needs.

Please let us know if it meets your needs.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

MACRO:   Extract Fields from Copy of Excel Data [Example]

-~~~ VER: 1.0    2021-02-27 ~~~
Requires: KM 8.2.4+   macOS 10.11 (El Capitan)+
(Macro was written & tested using KM 9.0+ on macOS 10.14.5 (Mojave))

DOWNLOAD Macro File:

Extract Fields from Copy of Excel Data [Example].kmmacros
Note: This Macro was uploaded in a DISABLED state. You must enable before it can be triggered.

ReleaseNotes

Author.@JMichaelTX

PURPOSE:

  • Extract Fields from Copy of Excel Data [Example]

HOW TO USE

  1. First, make sure you have followed instructions in the Macro Setup below.
  2. Select the Cells in Excel You want to Process
  3. Trigger this macro.

MACRO SETUP

.
Make These Changes to this Macro

  1. Assign a Trigger to this macro.
  2. Move this macro to a Macro Group that is only Active when you need this Macro.
  3. ENABLE this Macro, and the Macro Group it is in.
    .
  • REVIEW/CHANGE THE FOLLOWING MACRO ACTIONS:
    (all shown in the magenta color)
    • RegEx Search: Extract Tab-Delimited Data
      • Change the KM Variable Names as you like

REQUIRES:

  1. KM 9.0+ (may work in KM 8.2+ in some cases)
  2. macOS 10.11.6 (El Capitan)+

TAGS: @Search @RegEx @Extract @Example

Keyboard Maestro Export.png1170×1438 140 KB

2 Likes
10

Ah. That's a simpler way. I should have came up with this solution from the beginning. But I was passionate about sharing a plugin and was myself brought away with a plugin-like solution...