Excel formula

Kazdima

Board Regular
Joined
Oct 15, 2010
Messages
226
Hello,<o:p></o:p>
<o:p> </o:p>
I need to build a formula, which can combine two data from Aand C into B.<o:p></o:p>
Data in C can be from 0(zero) to ABSD (four letters).<o:p></o:p>
You can see in B how the result should look like.<o:p></o:p>
Account (A)<o:p></o:p>
Combined GL (B)<o:p></o:p>
Cost_Center (C)<o:p></o:p>
01-001-1-000-0000 Legislative Grants<o:p></o:p>
01-001-1-000-0000-0 Legislative Grants<o:p></o:p>
0<o:p></o:p>
01-011-1-000-0062 Other Grants-Parents reac<o:p></o:p>
01-011-1-000-0062-STM Other Grants - Parents reac<o:p></o:p>
STM<o:p></o:p>
<tbody> </tbody>
<o:p> </o:p>
Thank you.<o:p></o:p>
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
HI Jonmo1,

Thank you, formula is working, only some edits needed.
This is how it should be:
01-001-1-000-0000-0 Legislative Grants

And this how it looks like after formula worked:
01-001-1-000-0000- 0 Legislative Grants

It looks like there is one extra space after "-" and "0". because search is not working.

<tbody>
</tbody><colgroup><col></colgroup>


<tbody>
</tbody><colgroup><col></colgroup>
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top