Modify Text Issue

[jwillard]

I am trying to modify 2 fields (Customer name, customer number) that are shown below and add some text also.

AUTO SALON FIN363

I need the output to look like

1,,"auto salon","fin363",,,,,,,,,,,,,,,$0.00,,,0

Note that the numbers, quotes, and commas must all be added. I have tried using cocatenate but either I am doing it wrong or maybe that is not the correct function?
Any help will be greatly appreciated. I am ready to pull my hair out with this one.......


Boycott Sony

 

[dhulbert]

Need a bit more to work on here, is th einfo in access, excel word or what.
Do you have the other data ie the numbers sored or does it have to be added on the fly is it the same value on every row?

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[TonyJollans]

I assume that you are using Excel as you refer to Concatenate. Do you really want that text string all in a cell? If so (and assuming your values are in A1 & B1) ...

[blue][tt] =CONCATENATE("1,,",LOWER(A1),",",LOWER(B1),",,,,,,,,,,,,,,,$0.00,,,0")("1,,",A1,A2,",,,,,",3,",,,")[/tt][/blue]

... or just ...

[blue][tt] ="1,,"&LOWER(A1)&","&LOWER(B1)&",,,,,,,,,,,,,,,$0.00,,,0"[/tt][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[TonyJollans]

Whoops! That should be ...

[blue][tt] =CONCATENATE("1,,",LOWER(A1),",",LOWER(B1),",,,,,,,,,,,,,,,$0.00,,,0")[/tt][/blue]

... or just ...

[blue][tt] ="1,,"&LOWER(A1)&","&LOWER(B1)&",,,,,,,,,,,,,,,$0.00,,,0"[/tt][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[bluedragon2]

If Auto Salon is in cell A1 and FIN363 is in cell A2 then:

="1,,"&CHAR(34)&A1&CHAR(34)&","&CHAR(34)&A2&CHAR(34)&",,,,,,,,,,,,,,,$0.00,,,0"



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AnotherHiggins]

It looks like you are trying to create a comma-separated file. Will there ever be anything between any of the other commas? What cells are "AUTO SALON" and "FIN363" currently in?

There are multiple ways to proceed.

If no other fields have to be added and the "1", "$0.00" and "0" are constant, then you could use the following:[COLOR=blue white]
="1,,""" & A1 & """,""" & B1 & """" & ",,,,,,,,,,,,,,,$0.00,,,0"[/color]

Another - and probably easier - way to go would be to put the desired fields in the proper columns then save the file as Type: CSV.

Put:
-1 in column A
-AUTO SALON in column C
-fin363 in column D
-$0.00 in column S
-0 in column V

Go to File > Save As and change the Save as type to CSV.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[jwillard]

Thanks for the quick replies.
Yes it is Excel I am using.

Tony, The first one always shows the formula and not the results.

The second one does not put the double quotes around the info. This is what I got with the 2nd one

chrysler plymouth,103aca,,,,,,,,,,,,,,,$0.00,,,0

Very close all I need is the quotes around each piece of data.
Like so. thanks again

"chrysler plymouth","103aca",,,,,,,,,,,,,,,$0.00,,,0

Boycott Sony

 

[jwillard]

The only one thats not a constant would be the number 1 in the beginning. That would need to count by 1 as I go down the sheet.

Bluedragon and anotherhiggins - Both seem to work except for my failing to mention the above variable about the first column counting.

Thanks again for all the replies.



Boycott Sony

 

[jwillard]

Im very close. If I put a 1 in a cell by itself and then drag down to fill cells below, how can I make it count 1 2 3 and so on instead of it just putting a 1 in all the cells.

Hope i explained what I want there clearly...

Boycott Sony

 

[TonyJollans]

Sorry. I missed that detail ...

[blue][tt] ="1,,"&[/tt][red][tt]""""&[/tt][/red][tt]LOWER(A1)&[/tt][red][tt]""""&[/tt][/red][tt]","&[/tt][red][tt]""""&[/tt][/red][tt]LOWER(B1)&[/tt][red][tt]""""&[/tt][/red][tt]",,,,,,,,,,,,,,,$0.00,,,0"[/tt][/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[AnotherHiggins]

Hold down Ctrl while you drag.

Alternatively, put in the formula [COLOR=blue white]=Row()[/color] and drag that down. This has the advantage of not getting out of order if you delete rows.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[jwillard]

GOT IT!!!!!

Thanks so much to you all.....



Boycott Sony