Remove first and last comma

[rustyfishbones]

Hi All,

I have a field with comma separated values, like below

, PB , D , 28006 , 0 , 0 ,   9 ,

I am using SUBFIELD(MyField,',',) as MyNewField

however I keep getting blank values for the first and last comma

I want to remove the first and last comma while still using the above SUBFIELD Function

Regards

Alan

[rustyfishbones]

REPLACE will remove the first, but not the last comma it seems

[nizamsha]

can u send sample excel

[hic · Accepted solution]

If you know that the string always starts and ends with commas, you can use

     Subfield( Mid( MyField, 2, Len(MyField)-2 ), ',' )

HIC

[rustyfishbones]

Hi,

please see the attached excel file example

Regards

Alan

[#6]

Hi

what about cutting the first and last two chars similar to this:

=SUBFIELD(MID(InPut, 2, Len(Input)-2) , ',')

Hth

Roland

[rbecher]

Hi Alan,

what is the source of the field? Maybe you can just read it like a CSV source?

- Ralf

[rustyfishbones]

Thanks HIC, t

his is correct for what I asked

However I just noticed that all have a Comma at the End of the String but not all start with a Comma

[hic]

Then try

     Subfield(

          IF( Left(Trim(MyField),1)=',', Mid( Trim(MyField), 2, Len(Trim(MyField))-2 ), ',' ),

               Left( Trim(MyField), Len(Trim(MyField))-1 ),

           ',' ) )

HIC

[rustyfishbones]

Hi Ralf,

I am actually taking it from a Database!!

I believe it's structured that way because of the volume, it's an audit database of all activity in another system

Regards

Alan