Trimming a string containing numbers and text

[#1]

Hi I have the following data with the current data and expected data. What's the best way to get this output ?

Is it perhaps easier to do this in excel ?

Many thanks

[swuehl]

Try something like

LOAD

     Trim(SubField(CURRENT,'-',2)) as EXPECTED

FROM ....;

[#3]

Thanks Stefan. Any ideas how to do this in excel too ?

[swuehl]

Maybe like

https://blog.udemy.com/how-to-split-cells-in-excel/

[johnw · Accepted solution]

Maybe this in Excel?

=TRIM(MID(A2,FIND("-",A2)+2,99))

[#6]

Hi Swuehl,

in extension to this i have one more question

CURRENT	EXPECTED OUTPUT	if I enter these current field values in an input box, I need to show those values in a text box like 1-taxes|2-sales|5-employees|4-taxes|3-sales|6-employees
1 - taxes	1-taxes
2 - sales	2-sales
5 - employees	5-employees
4taxes	4-taxes
3sales	3-sales
6employees	6-employees

How do i achieve it?

maxgro‌; tresesco‌; stalwar1‌; jagan‌; hic‌

Thank you!

John

[tamilarasu]

You can also try.

=RIGHT(A2, LEN(A2)-FIND("-", A2))

[tamilarasu]

johndawson

Try something like this,

=Replace('$(vVarName)', chr(10),'|')

vVarName -> Variable name which you assigned in Input box.

Hope this helps.

[#9]

Thanks Tamil Nagaraj for your quick response.

here if you look at my expected out put you need to convert

1 - taxes
2 - sales
5 - employees
4taxes
3sales

6employees

to same format like

1-taxes

2-sales

5-employees

4-taxes

3-sales

6-employees

then your expression will work .

Thank you.

John

[tamilarasu]

Hi John,

Sorry. I have some important work to complete. I'll surely check and update after my work.