Parse Data

[DataPirate26]

Hello, 

Can anyone help me to Parse the Below required Data. I need only the 1st part of the data from the required field. 

For example : 1. ChemTreat [DHH-00-1100.148] 

Data I need: ChemTreat

2. X-Rite [DHH-00-1100.185 ]

Data i need: X-Rite 

So basically I need only the part before starting of [DHH-00....]

[binu_acs · Accepted solution]

@DataPirate26 

[DataPirate26]

@binu_acs . Thank you for a quick response and that did work Fine. 

[MarqueeCrew · Accepted solution]

@DataPirate26 ,

Using a Regular Expression is a quick solve.  RegEx is not always the way to go though.  I want you to consider the following alternative solution paths:

1.  PARSE tool:  You have a string that is delimited by the "[" square bracket character.  Your data likely has a SPACE in front of that character.  You can use a Text to Columns tool and then use a formula with a TRIM() function to remove the unwanted space.

2.  FindString() will find the " [" space followed by a left square bracket character and will return the position (0 based).  You can then use a LEFT() function to get the right number of characters:

          1         2         3
0123456789012345678901234567890
|----+----|----+----|----+----|
ChemTreat [DHH-00-1100.148] 
X-Rite [DHH-00-1100.185 ]

The findstring function will return 9 and 6 for these examples.  You can then use:

Left([ParseMePlease],Findstring([ParseMePlease]," ["))

The one formula (nesting) will get you your solution without the risk of RegEx coming to bite you.

Cheers,

Mark

@estherb47 

[DataPirate26]

@MarqueeCrew . This was perfect. Thank you