Supress extra spaces in a name 1

[victora]

I'm using an excel as my input. somebody combined a field (NAME) so that it has extra spaces between firstname and lastname. How can I eliminate them and output/print the name with just 1 space in between?

 

[alley211]

Use this formula:

local stringvar first :=
Left ({@name}, instr ({@name}, " &quot-1 );
local stringvar last :=
Mid ({@name}, instrrev ({@name}, " &quot+1);
first + " " + last

Hope this helps Christine
Crystal Training and Consulting
crosenbaum@Strafford.com

http://www.crystaltraining.com

 

[Ngolem]

try this formula

@CompressName

StringVar startname := {table.ProblemName};
StringVar result := "";
numberVar StartPos ;
numberVar icount;

StartPos := instr(startname, " "
//this includes the first blank space
result := Left(startname,startPos);

for icount := StartPos + 1 to length(startName) do
(
if startname[icount] <> &quot; &quot; then
exit for;
);

result := result +
right(startname, length(startname) - icount + 1);

that should do it. Jim Broadbent

 

[SmpTraining]

Try copying and pasting this into a formula:

StringVar Text := &quot;John Smith&quot;;
Left (Text, Instr(text, &quot; &quot-1) + right (Text, InStrRev(Text, &quot; &quot-1)

You can of course comment out the first line and change the word 'Text' into the name of your field.

This formula will only work if NAME has only two names in it e.g. First Name and Last Name. If it has other things like Titles then it won't work.

It works by combining the Left word in NAME (i.e. up to the first space) with a single space, followed by the Last word in NAME (i.e everything following the last space).

Strictly speaking - this post should have appeared under formula help. Hope it works for you.
Steve Phillips, Crystal Consultant

 

[Ngolem]

I forgot about the reverse instr....much easier Jim Broadbent

 

[dgillz]

It might be easier to go to Excel and replace a double space &quot; &quot; with a single space &quot; &quot; globally for that column.

Go to the edit menu and choose &quot;replace&quot;. This takes about 3 seconds.

Of course if this is on ongoing issue, thats another story. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[victora]

thank u all guys. I appreciate all the replies. But here's the good news: I was researching on the original excel file if there is a function that will eliminate the extra spaces in a cell. And lo and behold!

=trim(cell) will do the trick

This works even if there is a Title or middle initial in the field.

though you guys might want to know...

 

[skuhlman]

it's not the prettiest, but if you build a formula with the following expression, replacing all of the periods with spaces (I used periods so you could see them) then it will show each occurance of multiple spaces as a single space. I think this will work for strings with up to 2,447 consecutive spaces.

REPLACE ALL event2 WITH replacestring(replacestring(replacestring(replacestring(replacestring(replacestring(fmlMyField,'................','.'),'........','.'),'....','.'),'..','.'),'..','.'),'..','.')