Text formula 2

[rhoneyfi]

Hello,
I am using CR 9.0. I have a report that shows the name of our business clients. Sometimes, their names look like this: Mega Warehouse, The

I need a formula that shuffles the name so it looks like this: The Mega Warehouse

About 5% of my records have this type of text formatting where it has the name of the business client followed by: , The

Thanks in advance for any help.

 

[mrudolph]

The following formula should take care of your situation. (you may need to tweak it some depending on extra spaces, but generally it should work). The InStr function is used to see if a comma exists and if so it then uses that as the "pivot point" in the name to extract the parts before and after the comma. If no comma is found, then it just outputs the Client name field as is. Just replace {Client.ClientName} below with your actual database field.

If InStr({Client.ClientName},',') > 0
Then Trim(Mid({Client.ClientName},InStr({Client.ClientName},',')+1)) + ' ' + Left({Client.ClientName},InStr({Client.ClientName},',')-1)
Else {Client.ClientName}

 

[synapsevampire]

Try:

if instr("warehouse, the",",") > 0 then
if ubound(split("warehouse, the",",")) > 1 then
trim(split("warehouse, the",",")[2])+" "+trim(split("warehouse, the",",")[1])
else
"warehouse, the"

Replacing "warehouse, the" with your field.

-k

 

[Reebo99]

Both the solutions above assume that there is only one comma in the company name. If, for instance, you also have company names along the lines of "Smith, Brown and Carpenter Laundry, The" then these will not work.

Use:

Local StringVar Input := "mega warehouse, the";
If UCase(Right(Input,5)) = ", THE" then
Trim(Mid(Input,InStrRev(Input,",")+1))&" "&Trim(Left(Input,InStrRev(Input,",")-1)) else
Input;

replace the item in bold with your field name.

Reebo