Adding a data from a cell prior to account number

[ashley72788]

Hi There,

I have a report that I run in a system at work and then save as a .txt file, use the text to columns in excel to format it how I need it and get the attached spreadsheet. This would work perfectly for me with an exception to one issue.

I changed a lot of the text on the report to protect privacy, but the information is formatted the same so I don’t think we should have issues finding a resolution.

This report has areas highlighted, basically what I need is for the spreadsheet to know when we are changing associations, and to assign the number next to association name in front of the unit number so that I can have the full account number.

Example: Line 14 shows a number (34-5678) in column C14, this number will change within the same association if there are multiple buildings and it will also change if we are looking at a new association. This number is the first 6 digits of an account number and goes along with the unit number (column A) I need to somehow have excel pull that number when it exists in that column and place it before the unit number so we have the full account number. There won’t always be a number there, it only appears when there is a new association or building change within the same association. Is there a way to do this?

Please provide instructions on how you created a formula, rather than copying and pasting one – if possible. I’d appreciate copying and pasting as well 

[Sinon05]

Without VBA, you can use a helper column. Add a column for the account number and use this:
=IF(ISBLANK($D$14),A16,$D$14&"-"&A16)

[ashley72788]

Thank you, this works for the first building but as soon as that number changes (see column D70) it doesn't produce the correct account number. I need the formula to look for an account number in column D a number that consists of xx-xxxx and to input whatever those numbers are to the formula. Column D will only have a number like that when the building or association changes other wise it will be client name or other data.

[ashley72788]

I need a formula that will search column D for a sting of numbers that will be different but always follow the same pattern ie: 12-3456, 34-5678, 45-6789

[ChemistB]

Starting in H12 copied down

=IF(ISNUMBER(SEARCH("??-????",C13)),C13,NA())
I formatted this in white so its not visible. Could also hide this column

Starting in I12 copied down
=IF(AND(A12>0,ISNUMBER(A12+0)), INDEX($H$12:$H12, MATCH("zzz", $H$12:$H12))&"-" & A12,"")
Does that work for you?