Excel formula

Kazdima

Board Regular
Joined
Oct 15, 2010
Messages
214
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>
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Never seen REPLACE used with 0 as the 3rd argument. Very clever!
 

Kazdima

Board Regular
Joined
Oct 15, 2010
Messages
214

ADVERTISEMENT

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>
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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"

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">01-001-1-000-0000 Legislative Grants</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">01-001-1-000-0000-0 Legislative Grants</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">01-011-1-000-0062 Other Grants-Parents reac</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">01-011-1-000-0062-STM Other Grants-Parents reac</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">STM</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=REPLACE(<font color="Blue">A1,18,0,"-"&C1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=REPLACE(<font color="Blue">A2,18,0,"-"&C2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Kazdima

Board Regular
Joined
Oct 15, 2010
Messages
214

ADVERTISEMENT

May be because I use a Table format?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Oct 15, 2010
Messages
214
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,109,335
Messages
5,528,096
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top