Help Needed for Formula To Trim

[system]

Hi,

I was wondering if there’s a way to Trim the following information:
“8/1/2018 Non-Voice 1 NH” or “06/15/2018 Non-Voice 1 NH”

Into:

“8/1/2018” or “06/15/2018”
“Non-Voice”
“1”
“NH”

Which will then be reflected into 4 separate columns.

Any help would be appreciated.

Thank you

---

iAmPatch (BOB member since 2018-08-17)

[system]

Use Pos() to find the spaces and combine it with Substr() to get the different bits you are interested in.

---

Nick Daniels (BOB member since 2002-08-15)

[MarkP]

That’s not trimming, it’s parsing and is something more usefully done before the reporting layer.

[system]

@Nick Daniels

Use Pos() to find the spaces and combine it with Substr() to get the different bits you are interested in.

Hi Nick,

I tried the Pos() but I’m not sure how to “wildcard” the date entered, I did try the Substr() and was able to get the first part (but I manually counted the strings).

---

iAmPatch (BOB member since 2018-08-17)

[system]

@Mark P

That’s not trimming, it’s parsing and is something more usefully done before the reporting layer.

Hi Mark,

When you said “usefully done before the reporting layer”; would this mean that the person using Taleo Portal should enter the information more “correctly”?

---

iAmPatch (BOB member since 2018-08-17)

[system]

Is there like a “Find” formula that can be used instead?

---

iAmPatch (BOB member since 2018-08-17)

[system]

The Pos function is very much like Find in other environments. There is also the Match function which allows wildcards, if you need it.

You need to be clear about what you are looking for - do you just want to break at each space? Will there always be exactly three spaces?

Something like this will split the first piece and the remainder:

[blank1] = pos(" ";[text])
[string1] = left([text];[blank1]-1)
[remainder1] = substr([text];[blank1]+1;999)

Then you need to do something similar three more times.

---

mikeil (BOB member since 2015-02-18)

[MarkP]

@iAmPatch

@Mark P

That’s not trimming, it’s parsing and is something more usefully done before the reporting layer.

Hi Mark,

When you said “usefully done before the reporting layer”; would this mean that the person using Taleo Portal should enter the information more “correctly”?

Yes, splitting a whole load of text out into separate columns should be done in the database. Webi is meant to be a drag and drop reporting tool, not drag, drop and try and figure out how to make sense of the data.

[system]

@mikeil

The Pos function is very much like Find in other environments. There is also the Match function which allows wildcards, if you need it.

You need to be clear about what you are looking for - do you just want to break at each space? Will there always be exactly three spaces?

Something like this will split the first piece and the remainder:

[blank1] = pos(" ";[text])
[string1] = left([text];[blank1]-1)
[remainder1] = substr([text];[blank1]+1;999)

Then you need to do something similar three more times.

There will always be exactly 3 spaces. And I got lost with the guide on how to split the data. But I’ll try to work on this. Thanks a lot for the input

---

iAmPatch (BOB member since 2018-08-17)

[system]

@Mark P

@iAmPatch

@Mark P

That’s not trimming, it’s parsing and is something more usefully done before the reporting layer.

Hi Mark,

When you said “usefully done before the reporting layer”; would this mean that the person using Taleo Portal should enter the information more “correctly”?

Yes, splitting a whole load of text out into separate columns should be done in the database. Webi is meant to be a drag and drop reporting tool, not drag, drop and try and figure out how to make sense of the data.

Sadly, users don’t listen well and doesn’t follow instructions But yeah, they should start to do things right in the database

---

iAmPatch (BOB member since 2018-08-17)

[system]

The steps are:

1. Find the first blank
2. Extract everything to the left of it
3. Save the segment to the right of it
4. Repeat the process with the right segment from step 3
5. Repeat the process with the right segment from step 4

and then you should have your four pieces.

---

mikeil (BOB member since 2015-02-18)

[system]

@mikeil

The steps are:

1. Find the first blank
2. Extract everything to the left of it
3. Save the segment to the right of it
4. Repeat the process with the right segment from step 3
5. Repeat the process with the right segment from step 4

and then you should have your four pieces.

OMG! You are awesome!!! The process is tedious; but it does get the job done. Thank you so much for elaborating the steps further.

---

iAmPatch (BOB member since 2018-08-17)