Need Help Removing "-" character from names, but not from numbers in a combined string
Hello All
I am having difficulty trying to separate "-" from names while leaving it in for negative numbers.
The original file is a PDF which I can not get in any other format.
Occasionally a name is followed by a number with no space between them. I can split them to separate the name from the number until I run into a name that has a hyphen or a negative number.
Examples of data after I trap the Name and Reg Pay line as one trap to split later:
| Header 1 | Header 2 | Header 3 |
|---|---|---|
| Original string | Name | Number |
| Jane Doe 425.36 | Jane Doe | 425.36 |
| Jane Smith-Doe 325.00 | Jane Smith-Doe | - 325.00 |
| Jane DoeDoe -170.15 | Jane Doe | -170.15 |
| Jane Smith-Doe852.15 | Jane Smith-Doe | -852.15 |
As you can see, the top and middle rows works out fine, but the second and last row give me false negative numbers.
Original Data PDF Structure:
EENum Name
Loc JobCode Reg Pay Overtime
Department 1234 Accounting
1234567 Jane Doe 425.36 12.25
99999 RP1234
1234568 Jane Smith-Doe 325.00 0.00
99999 RP1234
1234569 Jane DoeDoe -170.15 10.52
99999 RP1234
1234570 Jane Smith-Doe852.15 5.25
It is rows like that last one that are throwing me doing a normal capture.
Any suggestions would be appreciated. Especially ones that let me trap the last row and split the Reg Pay out automatically.
I am using version 13.4 of Monarch
Thank you
Aaron
Best Answer
-
Hi Aaron,
I'm away from my PC today, so can't work out the exact formula. But I think the following approach would work.
First, use the Rsplit based on the - character.
Strip all spaces.
Then test the 2nd character using Isalpha.
If it is, you know the - is in a name, so strip all non numeric characters.
If it is not, then it should be the final result.
I think the initial rsplit on - will give you an empty field if there is no -, so you may need to test that first using instr.
This works in my mind! But I need to test it to be sure and come up with a calculated field.
Regards,
Steve.
Get Outlook for iOS<https://aka.ms/o0ukef>
0
Altair EmployeeAnswers
-
hi A MAN,
Have you tinkered with PDF Options before loading the PDF? The PDF Options like stretch, monospace helps aligning columns and changing spacing for easier templating.
For your PDF, if you can just add a space before name and number then it would be easier to split those two using RSplit function or other string functions.
HTH
Mela
0 -
Hi Aaron,
I'm away from my PC today, so can't work out the exact formula. But I think the following approach would work.
First, use the Rsplit based on the - character.
Strip all spaces.
Then test the 2nd character using Isalpha.
If it is, you know the - is in a name, so strip all non numeric characters.
If it is not, then it should be the final result.
I think the initial rsplit on - will give you an empty field if there is no -, so you may need to test that first using instr.
This works in my mind! But I need to test it to be sure and come up with a calculated field.
Regards,
Steve.
Get Outlook for iOS<https://aka.ms/o0ukef>
0 -
Altair Forum User said:
Hi Aaron,
I'm away from my PC today, so can't work out the exact formula. But I think the following approach would work.
First, use the Rsplit based on the - character.
Strip all spaces.
Then test the 2nd character using Isalpha.
If it is, you know the - is in a name, so strip all non numeric characters.
If it is not, then it should be the final result.
I think the initial rsplit on - will give you an empty field if there is no -, so you may need to test that first using instr.
This works in my mind! But I need to test it to be sure and come up with a calculated field.
Regards,
Steve.
Get Outlook for iOS<https://aka.ms/o0ukef>
Hello Steve
I’ll give you suggestions a try and see if I can get the results I need.
<Steve I was able to use Lsplit/Rsplit and then an If statement to get what I needed.
Thanks for the suggestions.
Thank you
Aaron
0 -
Altair Forum User said:
hi A MAN,
Have you tinkered with PDF Options before loading the PDF? The PDF Options like stretch, monospace helps aligning columns and changing spacing for easier templating.
For your PDF, if you can just add a space before name and number then it would be easier to split those two using RSplit function or other string functions.
HTH
Mela
Hello Mela
Yes, I did try the different options for PDFs. It looks like the original data merges some of the Reg Pay data when the names are too long to fit in the name column. I actual had to stretch the PDF to be able to get most of the data.
Thanks for taking a look.
Aaron
0