Excel formula

[Kazdima]

Hello,<o></o>
<o> </o>
I need to build a formula, which can combine two data from Aand C into B.<o></o>
Data in C can be from 0(zero) to ABSD (four letters).<o></o>
You can see in B how the result should look like.<o></o>
[TABLE="class: MsoNormalTable, width: 476"]
<tbody>[TR]
[TD="width: 350, bgcolor: transparent"] Account (A)<o></o>
[/TD]
[TD="width: 363, bgcolor: transparent"] Combined GL (B)<o></o>
[/TD]
[TD="width: 80, bgcolor: transparent"] Cost_Center (C)<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 350, bgcolor: transparent"] 01-001-1-000-0000 Legislative Grants<o></o>
[/TD]
[TD="width: 363, bgcolor: transparent"] 01-001-1-000-0000-0 Legislative Grants<o></o>
[/TD]
[TD="width: 80, bgcolor: transparent"] 0<o></o>
[/TD]
[/TR]
[TR]
[TD="width: 350, bgcolor: transparent"] 01-011-1-000-0062 Other Grants-Parents reac<o></o>
[/TD]
[TD="width: 363, bgcolor: transparent"] 01-011-1-000-0062-STM Other Grants - Parents reac<o></o>
[/TD]
[TD="width: 80, bgcolor: transparent"] STM<o></o>
[/TD]
[/TR]
</tbody>[/TABLE]
<o> </o>
Thank you.<o></o>

 

[Kazdima]

Still I have two extra spaces between "-" and "0", and between "0" and "Legislative".
So, there should be no space between "0" and "-", and one space between "0" and "L"

 

[Jonmo1]

'Still' ? What have you tried so far to remove them ? Did you try my suggestion(s) from the last post?

What do these return?
=CODE(LEFT(C1,1))
=CODE(MID(C1,2,1))

These should tell us what exactly those 2 characters are, if using TRIM didn't work then they are NOT spaces.

 

[Kazdima]

When I used this formula: =REPLACE(A1,18,0,"-"&TRIM(C1))<o></o>
I got thisresult: 01-001-1-000-0000-0 Legislative Grants (Two spaces instead of onebetween “0” and “L”<o></o>
<o> </o>
When IUSED THIS FORMULA: =REPLACE(A1,18,0,"-"&RIGHT(C1,LEN(C1)-2))<o></o>
<o> </o>
01-001-1-000-0000-0 Legislative Grants (Space between “-“and “0” and the same as above<o></o>
<o> </o>
Yourlast message I used as it is:<o></o>
Code(left(c1,1)) returned number 32<o></o>
Code(mid(c1,2,1)) returned number 109<o></o>

 

[Jonmo1]

It's going to be difficult to figure it out without actually seeing the real underlying data (in Excel, not written in a forum)
Can you post a sample file to a file sharing site (desensitize your data).

Maybe explaining what the basic formula is actually doing will help you sort it out..

=REPLACE(A1,18,0,"-"&C1)

The & symbol is short for the concatenate function. It joins 2 text strings together into 1.
so "-"&C1 is joining a hyphen with whatever is in C1.
If C1 is 0, then "-"&C1 = "-0"

=REPLACE(A1,18,0,"-0")

Replace is INSERTING the string "-0" between the 18th and 19th characters in A1.

Hope that helps.

 

[Kazdima]

Thank you, explanation helps-

But, for now the best result gives this one.

This formula: =REPLACE(A1,18,0,"-"&TRIM(C1))
ONLY we need to reduce one more space between “0” and “L”
I got this result: 01-001-1-000-0000-0 Legislative Grants (Two spaces instead of one between “0” and “L”

 

[Kazdima]

I found solution. Just was playing with numbers- Instead of "0", should be 1
[TABLE="width: 354"]
<tbody>[TR]
[TD]REPLACE(A1,18,1,"-"&TRIM(C1))

Thank you. Without your help I would not do it.[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Jonmo1]

You're welcome.