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>

 

[Jonmo1]

Try

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

 

[njimack]

Never seen REPLACE used with 0 as the 3rd argument. Very clever!

 

[Jonmo1]

Never seen REPLACE used with 0 as the 3rd argument. Very clever!

It sort of becomes the 'INSERT' function.

 

[Kazdima]

HI Jonmo1,

Thank you, formula is working, only some edits needed.
This is how it should be: [TABLE="width: 349"]
<tbody>[TR]
[TD]01-001-1-000-0000-0 Legislative Grants

And this how it looks like after formula worked:
[TABLE="width: 607"]
<tbody>[TR]
[TD]01-001-1-000-0000- 0 Legislative Grants

It looks like there is one extra space after "-" and "0". because search is not working.[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Jonmo1]

It works fine for me
If you're seeing a space between the - and string from C, then that space exists in the value in C. So C1 is " 0" not just "0"

Book1
	A	B	C
1	01-001-1-000-0000 Legislative Grants	01-001-1-000-0000-0 Legislative Grants	0
2	01-011-1-000-0062 Other Grants-Parents reac	01-011-1-000-0062-STM Other Grants-Parents reac	STM
Sheet1

Cell Formulas
Range	Formula
B1	=REPLACE(A1,18,0,"-"&C1)
B2	=REPLACE(A2,18,0,"-"&C2)

 

[Kazdima]

May be because I use a Table format?

 

[Jonmo1]

I don't see how the data being a table would matter.

I only see 2 possible reasons for that space to show up between the - and the contents from C.

1) You mistyped the formula, and added the space there
=REPLACE(A1,18,0,"- "&C1)
Should be
=REPLACE(A1,18,0,"-"&C1)

2) The space exists in C1
What does this return
=C1=0

 

[Kazdima]

You are right... there are 2 spaces in C.. This data file is being downloaded from ERP into Excel.
Is it anyway to reduce those spaces in C within formula?

 

[Jonmo1]

If they are actually spaces, and not some other non printable character, you can use TRIM

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

If it's not an actual space character, is it consistently always 2 of them at the beginning of every value in C ?
If so, you could do this

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