Concatenate cells using logic 2

[ewithoud]

Hello,

I have a question for excel.

I am using a pdf scrubbing software to get PDF data. However the data generated is not the same as is usable for our system. Therefore it needs to be adjusted.

The data looks as follows

cell 1 Cell 2 our system
XXX -YYYYYY-ZZ(Z) XXX YYYYYYZZ(Z)
XXX -YYYYY-ZZ(Z) XXX YYYYYZZ(Z)

(the (z) means optional both zzz and zz can occur)

so as you can see there is a problem to concatenate cell1 with cell2. As you see the number of spaces between between cell1 and cell2 depends on the number of characters of Y.

please see an extract below of data that would need to be changed.

Cell1 Cell2

4M5T -14240-EDB
7M5T -14A40-ED
4M5T -14240-EDD
4M5T -14A2C0-ABD
3M5T -14A280-AB
8M5T -14A280-AC

Any ideas on how this can be done?

Many thanks,

Erik Withoud

 

[AnotherHiggins]

-> As you see the number of spaces between between cell1 and cell2 depends on the number of characters of Y.

I'm afraid that I can't see that. First, the number of spaces is not evident in a post. More importantly, those two examples aren't enough for us to determine how the number of Ys should affect the number of spaces. 5 Ys results in two spaces, 6 Ys results in 2 spaces. Where's the logic?

Are you just trying to ensure that each cell contains the same number of spaces?

Please tell us what the relationship is between the number of Ys and the number of required spaces.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ewithoud]

Sorry for the confusion.

5 Y's equals 3 spaces, 6 Y's equals 2 spaces.

so for instance

4M5T -14240-EDB becomes 4M5T///14240EDB

and

3M5T -14A280-AB becomes 3M5T//14A280AB

(/ are spaces)

Does this make it clear?

Thanks for your help.

 

[GlennUK]

Something like:

=A1&REPT(" ",IF(SEARCH("-",B1,2)=7,3,2))&B1

, maybe?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[AnotherHiggins]

Will there only ever be 5 or 6 Ys? Will there ever be 3, 4, 7, 8, etc.?

Assuming it will only be 5 or 6, then:
[tab]=A1 & IF(SEARCH("-", B1, 2) = 7, "///", "//") & SUBSTITUTE(B1, "-", "")


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

I wrote a formula, got called away, and didn't check for new posts before hitting submit. You'd think I'd have learned better by now.

I like Glenn's use or REPT better than my IF. But you don't want hyphens, so you'll use the last bit of my formula with the SUBSTITUTE.

The end result will look like:
[tab][COLOR=blue white]=A1 & REPT(" ", IF(SEARCH("-", B1, 2) = 7, 3, 2)) & SUBSTITUTE(B1, "-", "")[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ewithoud]

Thanks Both,

this was really helpful it works now!

kind regards,

Erik Withoud